Sql query that sorts data by top quantity per category

[Hardz]

Hi, How to sort this sample data and query below by quantity and by category?: Select Qty, Category From Inv1 Order By Qty Desc, Category Qty Category 65 Cat3 55 Cat3 50 Cat2 45 Cat3 40 Cat1 30 Cat1 25 Cat1 25 Cat2 15 Cat1 15 Cat2 so that the output would be look like this: Qty Category 65 Cat3 55 Cat3 45 Cat3 50 Cat2 25 Cat2 15 Cat2 40 Cat1 30 Cat1 25 Cat1 15 Cat1 Thanks in advance! Hardz

[PIEBALDconsult]

Perhaps ORDER BY Category,Qty DESC ?

[__TR__]

Select Qty, Category From Inv1 Order By Category DESC, Qty DESC

[Hardz]

Hi, Thanks for the reply, but what I want to accomplish is that to determine the highest Qty then prioritized the Category that has the highest Qty, from this sample Cat3 contains the highest Qty(65) then sorted from high to low, next is Cat2(50), and Cat1(40). If I changed for ex. the Qty at cat1(40) to 95 then Cat1 now is the first priority of sorting, then Cat2 and so on, so the sorting may varies from high to low no. of Qty per Category, not by Category per Qty. Regards, Hardz

[Luc Pattyn]

This works fine for SQL Server:

SELECT Qty, t1.cat FROM Inv1 AS t1
INNER JOIN
(SELECT MAX(Qty) AS mq, cat FROM Inv1 GROUP BY cat) AS t2
ON t1.cat=t2.cat
ORDER BY mq DESC, Qty DESC

However, you didn't fully specify what you want: when two categories have equal maxima, what should happen? The above may intertwine such categories! This wouldn't:

SELECT Qty, t1.cat FROM Inv1 AS t1
INNER JOIN
(SELECT MAX(Qty) AS mq, cat FROM Inv1 GROUP BY cat) AS t2
ON t1.cat=t2.cat
ORDER BY mq DESC, cat, Qty DESC

:)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[Hardz]

Hi Luc, Your 2nd code worked perfect for me, coz you're right for your assumption that when there's an equal maxima between these categories it will automatically sorted by category. Thank you very much. Regards, Hardz