February 1, 2013 at 9:35 am
Hello -
I am struggling to figure out an efficient way to write this query. Here is the scenario:
1) My Data:
Store Number | Store Name | Product Name | Price
1 | MyStore1 | MyProduct1 | 25.99
2 | MyStore2 | MyProduct1 | 29.99
3 | MyStore1 | MyProduct2 | 12.99
2) Desired Result - Group by product name and choose the minimum price along with the store number and store name associated with that minimum price. For Example, the desired query result for the three rows above would be:
Store Number | Store Name | Product Name | Price
1 | MyStore1 | MyProduct1 | 25.99
3 | MyStore1 | MyProduct2 | 12.99
Any ideas on best way to accomplish this? Thanks!
February 1, 2013 at 11:30 am
You would write a two step Select...the first select being a Subquery/CTE that uses either Window Functions or the min(price) grouped by product name. Then the second select would be to use the first select with a where clause (in case of window function) or a join back to parent table (in case of group by).
In case you use a window function, be aware that ROW_NUMBER would give you only one row, while RANK or DENSE RANK would give you multiple rows. So if you have a product with the lowest price in multiple records then ROW_NUMBER would give you only one record, while RANK/DENSE RANK would give you all the records that qualify.
Here are the skeletons:
1. WINDOW FUNCTION
;WITH D(Column List) AS
(SELECT list of columns you want in the final output, appropriate WINDOW FUNCTION FROM YourTable)
SELECT Columns FROM D WHERE WINDOW_FUNCTION_COLUMN = 1
In your window function i would use asc order for minimum or lower or earlier data and desc for maximum, higher or latest data
2.Group By in Subquery/CTE
SELECT P.ColumnList, MP.GroupedOnColumn FROM YourTable P INNER JOIN (SELECT Columns_You_Want_ToGroupOn, MIN(Grouped_on_Column) AS Grouped_on_Column FROM YourTable GROUP BY Columns_You_Want_ToGroupOn) AS MP JOIN ON (P.Columns_You_Want_ToGroupOn = MP.Columns_You_Want_ToGroupOn AND P.Grouped_on_Column = MP.Grouped_on_Column
I intentionally provided the skeletons...
I would go with the window function approach in most cases.
February 1, 2013 at 12:29 pm
Thanks Sam, this was very helpful! My first time using CTEs or Windows Functions....pretty slick!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply