Order by 5 consecutive rows alternating between results

Question

I'm trying to select from two categories, but I want to alternate between categories by grouping five of each categories.

Here are the tables

SELECT MC.main_category_id, MC.main_category_name, MC.order_by_num AS MC_Order,
C.category_id, C.category_name, C.order_by_num AS C_Order, C.gift, I.item_id,                                            I.model_num, I.item_title, B.brand_name, I.price, I.suggested_price, i.description 
    FROM dbo.tblMainCategories AS MC
    INNER JOIN dbo.tblCategories AS C ON MC.main_category_id = C.main_category_id
    INNER JOIN dbo.tblItemCategories AS IC ON MC.main_category_id = IC.main_category_id
    AND C.category_id = IC.category_id 
    INNER JOIN dbo.tblItems AS I ON IC.ic_item_id = I.item_id 
    INNER JOIN dbo.tblBrands AS B ON I.brand_id = B.brand_id
WHERE (MC.store_id = 14) AND (IC.store_id = 14) AND I.isActive = 1 
And MC.main_category_id in (1,2)
ORDER BY MC.order_by_num, C.order_by_num, I.order_by_num,I.price

How can I sort this result that it should be ordered by 5 of MainCategory 1 then 5 of MainCategory 2 and then 5 of MainCategory 1 and continue 5 of each until the end.

MainCategory 1 has much more items than MainCategory 2. the rest of the MainCategory 1 should also be at the bottom.

Answer 1

You could try something like this. Just be sure your results are order the way you want when inserting them into the SQL tables. Break the query into 2 duplicates that insert the results into some temp tables. Then you can iterate through the results inserting each 5 into a third results table.

Here is an example of the approach I took with a db I had available to me.

create table #result(AccountID int, AccountTypeID int, AccountName varchar(128))


select AccountID, ab.AccountTypeID, AccountName INTO #Temp from AccountBase ab
join AccountType at on ab.AccountTypeId = at.AccountTypeId
where ab.AccountTypeId in (1)
order by AccountName

select AccountID, ab.AccountTypeID, AccountName INTO #Temp2 from AccountBase ab
join AccountType at on ab.AccountTypeId = at.AccountTypeId
where ab.AccountTypeId in (2)
order by AccountName



While ((select count(*) from #Temp) > 0 or (select count(*) from #Temp2) > 0)
begin 

    If (select count(*) from #Temp) > 0
        Begin
            insert into #result select Top(5) * from #Temp 
            delete top(5) from #Temp 
        End
    If (select count(*) from #Temp2) > 0
        Begin
            insert into #result select Top(5) * from #Temp2
            delete top(5) from #Temp2 
        End
End

select * from #result

drop table #Temp
drop table #Temp2
drop table #result

I believe everything is supported by SQL 2000.