I have a list of items
ItemName Manufacturer TopSalesUnit
Item1 A 100
Item2 A 80
Item3 A 60
Item4 B 70
Item5 B 50
Item6 B 30
Item7 C 10
Item8 C 05
I would like the records to be ordered so that the highest TopSalesUnit 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 shown, etc.:
ItemName Manufacturer TopSalesUnit
Item1 A 100
Item4 B 070
Item7 C 010
Item2 A 080
Item5 B 050
Item8 C 005
Item3 A 060
Item6 B 030
How to write a query in T-SQL to achieve it?
try:
DECLARE @YourTable table (ItemName varchar(10), Manufacturer char(1), TopSalesUnit int)
INSERT @YourTable VALUES ('Item1','A ',100)
INSERT @YourTable VALUES ('Item2','A ',80)
INSERT @YourTable VALUES ('Item3','A ',60)
INSERT @YourTable VALUES ('Item4','B ',70)
INSERT @YourTable VALUES ('Item5','B ',50)
INSERT @YourTable VALUES ('Item6','B ',30)
INSERT @YourTable VALUES ('Item7','C ',10)
INSERT @YourTable VALUES ('Item8','C ',05)
SELECT
dt.ItemName,dt.Manufacturer,dt.TopSalesUnit
FROM (SELECT
ItemName,Manufacturer,TopSalesUnit,ROW_NUMBER() OVER(PARTITION BY Manufacturer ORDER BY TopSalesUnit DESC) AS RowNumber
FROM @YourTable
) dt
ORDER BY dt.RowNumber,dt.Manufacturer
OUTPUT:
ItemName Manufacturer TopSalesUnit
---------- ------------ ------------
Item1 A 100
Item4 B 70
Item7 C 10
Item2 A 80
Item5 B 50
Item8 C 5
Item3 A 60
Item6 B 30
(8 row(s) affected)
Try this:
SELECT *,
( SELECT COUNT(*) FROM Items b
WHERE b.Manufacturer = Items.Manufacturer
AND b.TopSalesUnit > Items.TopSalesUnit )
AS RankInManufacturer
FROM Items
ORDER BY RankInManufacturer, TopSalesUnit DESC
This adds a new computed column that ranks the "TopSalesUnit" fields within each "Manufacturer".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With