brain teaser - stagger the order of the results

[mjweiner]

This one has been stumping me for several days. I can run a query that
returns several different items from several different manufacturers,
each with a ranking score. Each manufacturer can have any number of
items:

Item_Name Manufacturer rank
Item 1 Manu_A 82
Item 2 Manu_A 65
Item 3 Manu_A 41
Item 4 Manu_B 32
Item 5 Manu_C 21
Item 6 Manu_B 19

However, I would like the records to be ordered so that the highest
ranking item is shown first, then the next highest item from a
different manufacturer is shown second, then the next highest item from
a third manufacturer is show, etc.:

Item 1 Manu_A 82
Item 4 Manu_B 32
Item 5 Manu_C 21
Item 2 Manu_A 65
Item 6 Manu_B 19
Item 3 Manu_A 41

Does anyone have any thoughts on how to order the results in this
fashion?

thanks,

Matt Weiner

[David Portas]

It would be useful to see how you compute the Rank value. Perhaps your
result is possible by adapting your existing query. From what you've
given us, try this:

SELECT T1.item_name, T1.manufacturer, T1.rank
FROM (/* Your query */) AS T1
JOIN (/* Your query */) AS T2
ON T1.manufacturer = T2.manufacturer
AND T1.rank <= T2.rank
GROUP BY T1.item_name, T1.manufacturer, T1.rank
ORDER BY COUNT(*), T1.rank DESC

Just substitute your query or a view that contains it after the FROM
and JOIN.

--
David Portas
SQL Server MVP
--

[mjweiner]

thanks. It works great. I appreciate the help.

Matt