SQL Top n By Group
-
I am trying to retrieve the product, price, and date_despatched for the last three orders (date_despatched) for each product. There are two tables involved: opheadm (Sales Order Header) date_despatched order_no opdetm (Sales Order Detail) order_no product net_price Desired Result product_a 2.999 2012/03/23 product_a 2.600 2012/02/01 product_a 3.812 2012/01/23 product_b 1.456 2012/04/01 product_b 4.786 2012/02/13 product_b 2.563 2012/10/10 ... I have seen two approaches, but cant get either to work (since in this case there needs to be a join on the header and detail tables) a) OVER PARTITION BY (below is nowhere near correct - just to show example)
SELECT product_code, price, date_entered
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY product_code ORDER BY date_entered DESC) AS OrderedDate,* FROM opdetm ) AS ordlist
WHERE ordlist.OrderedDate<=3;b) SELECT.. WHERE IN (SELECT TOP n...)
SELECT E.scheme_opdetm.product, scheme_opdetm.net_price, opheadm.date_despatched
FROM scheme_opdetm, scheme_opheadm AS E
WHERE E.date_despatched IN (SELECT TOP 3 E2.date_despatched from scheme_opdetm, scheme_opheadm AS E2 WHERE E.product = E2.product Order by E2.date_despatched DESC)
ORDER BY E.product, net_price DESC;Questions: a) Which approach is best? Why? b) Please could someone assist with the query I need, and if possible a short explanation of how to construct this type of query and what the different parts do. I'd like to try to understand what I'm diong as opposed to just copying someones SQL :) Thanks in advance
-
I am trying to retrieve the product, price, and date_despatched for the last three orders (date_despatched) for each product. There are two tables involved: opheadm (Sales Order Header) date_despatched order_no opdetm (Sales Order Detail) order_no product net_price Desired Result product_a 2.999 2012/03/23 product_a 2.600 2012/02/01 product_a 3.812 2012/01/23 product_b 1.456 2012/04/01 product_b 4.786 2012/02/13 product_b 2.563 2012/10/10 ... I have seen two approaches, but cant get either to work (since in this case there needs to be a join on the header and detail tables) a) OVER PARTITION BY (below is nowhere near correct - just to show example)
SELECT product_code, price, date_entered
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY product_code ORDER BY date_entered DESC) AS OrderedDate,* FROM opdetm ) AS ordlist
WHERE ordlist.OrderedDate<=3;b) SELECT.. WHERE IN (SELECT TOP n...)
SELECT E.scheme_opdetm.product, scheme_opdetm.net_price, opheadm.date_despatched
FROM scheme_opdetm, scheme_opheadm AS E
WHERE E.date_despatched IN (SELECT TOP 3 E2.date_despatched from scheme_opdetm, scheme_opheadm AS E2 WHERE E.product = E2.product Order by E2.date_despatched DESC)
ORDER BY E.product, net_price DESC;Questions: a) Which approach is best? Why? b) Please could someone assist with the query I need, and if possible a short explanation of how to construct this type of query and what the different parts do. I'd like to try to understand what I'm diong as opposed to just copying someones SQL :) Thanks in advance
Take a look at this:
CREATE TABLE Opheadm (date_despatched date, order_num int)
create table opdetm (order_num int, product VARCHAR(100), net_price money)INSERT INTO Opheadm values
('3/23/2012', 4),
('2/1/2012', 3),
('1/23/2012', 2),
('1/25/2011', 1)INSERT INTO opdetm values
(4, 'prod_a', 2.99),
(3, 'prod_a', 2.60),
(2, 'prod_a', 3.80),
(1, 'prod_a', 1.20)SELECT top 3 a.product, a.net_price,
ROW_NUMBER() Over(order by b.date_despatched DESC) AS RowNum
from opdetm a
inner join Opheadm b
on a.order_num = b.order_num -
Take a look at this:
CREATE TABLE Opheadm (date_despatched date, order_num int)
create table opdetm (order_num int, product VARCHAR(100), net_price money)INSERT INTO Opheadm values
('3/23/2012', 4),
('2/1/2012', 3),
('1/23/2012', 2),
('1/25/2011', 1)INSERT INTO opdetm values
(4, 'prod_a', 2.99),
(3, 'prod_a', 2.60),
(2, 'prod_a', 3.80),
(1, 'prod_a', 1.20)SELECT top 3 a.product, a.net_price,
ROW_NUMBER() Over(order by b.date_despatched DESC) AS RowNum
from opdetm a
inner join Opheadm b
on a.order_num = b.order_numHi djj55 I tested the query and it did not return what I was expecting. It only returned three rows. I need the last three orders for every stock code. Maybe I need to
SELECT DISTINCT (product) from opdetm
or something like that? And then do the joins to opheadm and join to get the last three orders? There is another table (stockm) stock master which has product as a primary key, so I could use that as well, except I would also need to only select records from stockm where the warehouse = '06' Currently I am pulling all stockm records where warehouse = '06' into another datatable in my application with a separate query, then I want to loop through the result set of this query and populate the table from stockm with the prices, dates and quantities of the last three sales orders, as well as comparing the standard cost of the item with the last sales order, and calculating the difference between the last Sales order and the standard cost The end result is a grid showing ALL stock items in warehouse 06, with the following columns: warehouse, product, physical_qty, standard_material, Error, SO1Price, SO1Date, SO1Qty, SO2Price, SO2Date, SO2Qty, SO3Price, SO3Date, SO3Qty If an item in the stock master has no sales, it must be listed, but the all the SO fields must be blank.
-
Hi djj55 I tested the query and it did not return what I was expecting. It only returned three rows. I need the last three orders for every stock code. Maybe I need to
SELECT DISTINCT (product) from opdetm
or something like that? And then do the joins to opheadm and join to get the last three orders? There is another table (stockm) stock master which has product as a primary key, so I could use that as well, except I would also need to only select records from stockm where the warehouse = '06' Currently I am pulling all stockm records where warehouse = '06' into another datatable in my application with a separate query, then I want to loop through the result set of this query and populate the table from stockm with the prices, dates and quantities of the last three sales orders, as well as comparing the standard cost of the item with the last sales order, and calculating the difference between the last Sales order and the standard cost The end result is a grid showing ALL stock items in warehouse 06, with the following columns: warehouse, product, physical_qty, standard_material, Error, SO1Price, SO1Date, SO1Qty, SO2Price, SO2Date, SO2Qty, SO3Price, SO3Date, SO3Qty If an item in the stock master has no sales, it must be listed, but the all the SO fields must be blank.
Sorry, being at work I do not have a lot of time. I believe what is needed is a sub query so you can then use a WHERE clause to get only three of each. If you are using 2008 you might try WITH. This has not been tested but something like:
SELECT Produce, Net_Price
FROM (
SELECT a.product, a.net_price,
ROW_NUMBER() Over(order by b.date_despatched DESC) AS RowNum
from opdetm a
inner join Opheadm b
on a.order_num = b.order_num ) AS c
WHERE RowNum < 4 -
Sorry, being at work I do not have a lot of time. I believe what is needed is a sub query so you can then use a WHERE clause to get only three of each. If you are using 2008 you might try WITH. This has not been tested but something like:
SELECT Produce, Net_Price
FROM (
SELECT a.product, a.net_price,
ROW_NUMBER() Over(order by b.date_despatched DESC) AS RowNum
from opdetm a
inner join Opheadm b
on a.order_num = b.order_num ) AS c
WHERE RowNum < 4Also at work, but on my way home, will test when I get home - and yes, using 2008