Custom sort order within each group of the result set
-
DECLARE @ItemList TABLE (OrderID INT, ItemID INT, ItemDescription VARCHAR (100), ItemTypeID INT)
INSERT @ItemList (OrderID, ItemID, ItemDescription, ItemTypeID)
VALUES
-- 1st set of data
(1001, 1872, 'Ping Pong Table', 4006),
(1001, 1876, 'Ping Pong Balls', 4112),
(1001, 1873, 'Ping Pong Paddle', 4108),
(1001, 1901, 'Ping Pong Net', 4071),
-- 2nd set of data
(1002, 1977, 'Golf Kit Bag', 4006),
(1002, 1983, 'Golf Balls', 5322),
-- 3rd set of data
(1003, 2046, 'Basketball', 4006),
-- 4th set of data
(1004, 3122, 'Poker Table', 4006),
(1004, 3143, 'Poker Chips', 5018),
(1004, 3144, 'Pack of Cards', 3987),
-- 5th set of data
(1005, 4198, 'Tennis Kit Bag', 4006),
(1005, 4224, 'Can of Tennis Balls', 2097),
(1005, 4221, 'Tennis Racquet', 2998)
select * from @ItemList
DECLARE @ItemListOutput TABLE (OrderID INT, ItemID INT, ItemDescription VARCHAR (100), ItemTypeID INT, ItemSortOrder INT)
INSERT @ItemListOutput (OrderID, ItemID, ItemDescription, ItemTypeID, ItemSortOrder)
VALUES
-- 1st set of data
(1001, 1872, 'Ping Pong Table', 4006, 1),
(1001, 1873, 'Ping Pong Paddle', 4108, 2),
(1001, 1876, 'Ping Pong Balls', 4112, 3),
(1001, 1901, 'Ping Pong Net', 4071, 4),
-- 2nd set of data
(1002, 1977, 'Golf Kit Bag', 4006, 1),
(1002, 1983, 'Golf Balls', 5322, 2),
-- 3rd set of data
(1003, 2046, 'Basketball', 4006, 1),
-- 4th set of data
(1004, 3122, 'Poker Table', 4006, 1),
(1004, 3143, 'Poker Chips', 5018, 2),
(1004, 3144, 'Pack of Cards', 3987, 3),
-- 5th set of data
(1005, 4198, 'Tennis Kit Bag', 4006, 1),
(1005, 4221, 'Tennis Racquet', 2098, 2),
(1005, 4224, 'Can of Tennis Balls', 2997, 3)
SELECT * FROM @ItemListOutput
I have result set from one of my queries as shown in the output from
select * from @ItemList
Now, I want to be able to set a sort order for items within each OrderID based on the following criteria.
Order Item that has ItemTypeID = 4006 will always go to the top with sort order as 1. Each order will certainly have this entry (and it will only be just one item of this type for each order).
For all remaining items (where ItemTypeID <> 4006), they should be sorted by ItemID within this set (since sort order 1 will be taken by the above entry, these items will start to have sort order starting from 2)
My desired out put will look like the second result set from the attached code
select * from @ItemListOutput
I appreciate your advice on this. Thanks in advance.
- Rex
-
I hope that I interpreted well your requirements:
SELECT *, 1 AS ItemSortOrder
FROM @ItemList
WHERE ItemTypeID = 4006
UNION ALL
SELECT *,
(1 + ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY ItemID)) AS ItemSortOrder
FROM @ItemList
WHERE ItemTypeID <> 4006
ORDER BY OrderID ASC, ItemSortOrder ASC
___________________________
Do Not Optimize for Exceptions! -
@Milos.radivojevic - Your solutions works perfectly to take care of my requirement. Thank you very much.
- Rex
-
I don't think you have to go thru all that.
SELECT *
FROM @ItemListOutput
ORDER BY
OrderID,
CASE WHEN ItemTypeID = '4006' THEN 1 ELSE 2 END,
ItemID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Scott,
Thanks for your suggestion, but your query won't give me the sort order id for each individual item of an order. Please look at the last column of @ItemListOutput table from my script.
- Rex
-
RexHelios (3/20/2014)
Scott,Thanks for your suggestion, but your query won't give me the sort order id for each individual item of an order. Please look at the last column of @ItemListOutput table from my script.
- Rex
It sorted fine for me, and the results from my query and the "UNION ALL" query look identical to me, so I'm not sure what you're referring to.
Did you actually run my query and compare the results?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Scott,
I think we have a slight disconnect here :). Your query is selecting from @ItemListOutput, which is where I included "ItemSortOrder" column. I did this to show my desired result. My original result set in @ItemList table does not have this column. I need to generate "ItemSortOrder" column based on the way data is sorted.
- Rex
-
RexHelios (3/20/2014)
Scott,I think we have a slight disconnect here :). Your query is selecting from @ItemListOutput, which is where I included "ItemSortOrder" column. I did this to show my desired result. My original result set in @ItemList table does not have this column. I need to generate "ItemSortOrder" column based on the way data is sorted.
- Rex
Typo:
SELECT *
FROM @ItemList
ORDER BY
OrderID,
CASE WHEN ItemTypeID = '4006' THEN 1 ELSE 2 END,
ItemID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I think your query is still missing the new column, "ItemSortOrder". Sorry, if I am starting to get to your nerves...my apologies! 🙁
Please see the image below that shows what I have and what I need.
- Rex
-
Hi again,
You can use the statement provided by Scott, too to avoid usage of UNION ALL:
SELECT *,
ROW_NUMBER() OVER(PARTITION BY OrderID
ORDER BY CASE WHEN ItemTypeID = '4006' THEN 1 ELSE 2 END, ItemID)
AS ItemSortOrder
FROM @ItemList
It should return the same as the statement with UNION ALL...
___________________________
Do Not Optimize for Exceptions! -
No, I'm sorry, I didn't realize you literally wanted the sort order in the result.
We will have to use ROW_NUMBER() for that, but I don't think we have to use UNION ALL or to scan the input table twice.
Please try this:
SELECT
il.*,
ROW_NUMBER() OVER(PARTITION BY il.OrderID
ORDER BY CASE WHEN il.ItemTypeID = '4006' THEN 1 ELSE 2 END, il.ItemID) AS ItemSortOrder
FROM @ItemList il
ORDER BY
OrderID,
ItemSortOrder
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Fantastic! Thanks for being so patient, Scott. It's appreciated very much!
- Rex
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply