brain teaser - stagger the order of the results

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 WeinerIt 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
--|||thanks. It works great. I appreciate the help.

Matt