SQL Get Max for each group
-
Hi I posted a similar question to this here [^] however after looking at the data in more detail, the query has to be changed a lot. Also in the previous post, I could not get it to work. The select statement below is what i need except, this returns all the rows for each product, and all I need is the MAX date_despatched for EACH product
SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;Basically the Sales Order Header (scheme_opheadm) contains the Date Despatched, Sales Order, ans Status (which must be 8 for despatched orders). The Sales Order Detail (scheme_opdetm) has the Warehouse Product Net Price (There are also comments and other lines that I need to ignore in the Detail Table, hence the filters for Net Price > 0, Qty Despatched > 0, Warehouse = 06 (finished goods). How can I get only the details of the LAST despatch for EACH item?
-
Hi I posted a similar question to this here [^] however after looking at the data in more detail, the query has to be changed a lot. Also in the previous post, I could not get it to work. The select statement below is what i need except, this returns all the rows for each product, and all I need is the MAX date_despatched for EACH product
SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;Basically the Sales Order Header (scheme_opheadm) contains the Date Despatched, Sales Order, ans Status (which must be 8 for despatched orders). The Sales Order Detail (scheme_opdetm) has the Warehouse Product Net Price (There are also comments and other lines that I need to ignore in the Detail Table, hence the filters for Net Price > 0, Qty Despatched > 0, Warehouse = 06 (finished goods). How can I get only the details of the LAST despatch for EACH item?
Create a sub query that uses Row_Number() and Partition based in the id field in scheme_opdetm, selecting data from your transaction table and numbered/ordered by the date field. Join that to your main query and filter it on row_number = 1.
Never underestimate the power of human stupidity RAH
-
Create a sub query that uses Row_Number() and Partition based in the id field in scheme_opdetm, selecting data from your transaction table and numbered/ordered by the date field. Join that to your main query and filter it on row_number = 1.
Never underestimate the power of human stupidity RAH
Thanks Mycroft, but I have no idea how to do this. First, I'm not sure how to put in the PARTITION clause, Second, the Despatch date is in the header table (opheadm), with a Sales Order Num, but I want to Partition by Product not by Sales Order Num, and the product is in the Details Table (opdetm). This what I have tried so far, but I get an error "The column 'order_no' was specified multiple times for ordlist
SELECT opdetm.warehouse, opdetm.product, opdetm.net_price, opdetm.despatched_qty, opheadm.date_despatched
FROM vektron.scheme.opdetm INNER JOIN vektron.scheme.opheadm ON opdetm.order_no = opheadm.order_no ,
(SELECT ROW_NUMBER() OVER (PARTITION BY opheadm.order_num ORDER BY date_entered DESC) , * FROM vektron.scheme.opheadm inner join vektron.scheme.opdetm on opdetm.order_no = opheadm.order_no) as ordlist
WHERE(((opheadm.status) = '8') And ((opdetm.warehouse) = '06') And ((opdetm.net_price) > 0) And ((opdetm.despatched_qty) > 0)) and ordlist.order_no = 1 and opdetm.product = ordlist.product
ORDER BY opdetm.product, opheadm.date_despatched DESC -
Thanks Mycroft, but I have no idea how to do this. First, I'm not sure how to put in the PARTITION clause, Second, the Despatch date is in the header table (opheadm), with a Sales Order Num, but I want to Partition by Product not by Sales Order Num, and the product is in the Details Table (opdetm). This what I have tried so far, but I get an error "The column 'order_no' was specified multiple times for ordlist
SELECT opdetm.warehouse, opdetm.product, opdetm.net_price, opdetm.despatched_qty, opheadm.date_despatched
FROM vektron.scheme.opdetm INNER JOIN vektron.scheme.opheadm ON opdetm.order_no = opheadm.order_no ,
(SELECT ROW_NUMBER() OVER (PARTITION BY opheadm.order_num ORDER BY date_entered DESC) , * FROM vektron.scheme.opheadm inner join vektron.scheme.opdetm on opdetm.order_no = opheadm.order_no) as ordlist
WHERE(((opheadm.status) = '8') And ((opdetm.warehouse) = '06') And ((opdetm.net_price) > 0) And ((opdetm.despatched_qty) > 0)) and ordlist.order_no = 1 and opdetm.product = ordlist.product
ORDER BY opdetm.product, opheadm.date_despatched DESCWithout the DB I'm not going to be able to build the query, but here is how I would go about it. Create a query that returns you the minimum bits you need to do the partition job. That should be the ID, the date and the product fields. The ID to link back to your result set, the data to order by and the product to partition over. Once you have that right you put it aside and create another query that had the ID field and all the bits you want in your end result. Now join the 2 queries!
Never underestimate the power of human stupidity RAH
-
Hi I posted a similar question to this here [^] however after looking at the data in more detail, the query has to be changed a lot. Also in the previous post, I could not get it to work. The select statement below is what i need except, this returns all the rows for each product, and all I need is the MAX date_despatched for EACH product
SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;Basically the Sales Order Header (scheme_opheadm) contains the Date Despatched, Sales Order, ans Status (which must be 8 for despatched orders). The Sales Order Detail (scheme_opdetm) has the Warehouse Product Net Price (There are also comments and other lines that I need to ignore in the Detail Table, hence the filters for Net Price > 0, Qty Despatched > 0, Warehouse = 06 (finished goods). How can I get only the details of the LAST despatch for EACH item?
Try something like this:
WITH original_query AS (
SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;
)
,last_despatched AS (
SELECT product,Max(date_despatched) AS date_despatched
FROM original_query
GROUP BY product
)
SELECT o.warehouse, o.product, o.net_price, o.despatched_qty, o.date_despatched
FROM original_query o,last_despatched l
WHERE o.product = l.product
AND o.date_despatched = l.date_despatchedI love CTEs.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Try something like this:
WITH original_query AS (
SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;
)
,last_despatched AS (
SELECT product,Max(date_despatched) AS date_despatched
FROM original_query
GROUP BY product
)
SELECT o.warehouse, o.product, o.net_price, o.despatched_qty, o.date_despatched
FROM original_query o,last_despatched l
WHERE o.product = l.product
AND o.date_despatched = l.date_despatchedI love CTEs.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
Thanks so much Jorgen! That worked brilliantly. I did have to remove:
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;
becaue it gave an error (MSSQL 2008 R2): "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."
-
Thanks so much Jorgen! That worked brilliantly. I did have to remove:
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;
becaue it gave an error (MSSQL 2008 R2): "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."
Glad to help.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Try something like this:
WITH original_query AS (
SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;
)
,last_despatched AS (
SELECT product,Max(date_despatched) AS date_despatched
FROM original_query
GROUP BY product
)
SELECT o.warehouse, o.product, o.net_price, o.despatched_qty, o.date_despatched
FROM original_query o,last_despatched l
WHERE o.product = l.product
AND o.date_despatched = l.date_despatchedI love CTEs.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
SELECT b.RowNumber, iSrno, vUserCode, dLoggedDate FROM ( SELECT iSrno, vUserCode, dLoggedDate, ROW_NUMBER() OVER ( PARTITION BY vUserCode ORDER BY vUserCode DESC ) 'RowNumber' FROM HOUserLog) b WHERE RowNumber = 1 /* Here HOUserLog is the table from where the Group By MAX needs to be pick. ROw_Number() OVER ( PARTITION BY .... ) will generate the RowNumber according to the Group By Column you provided in ORDER BY ..... DESC Clause. Now pick the values for every RowNumber Column = 1 will get you the solution. MARK AS ANSWER IF WORKS
- Happy Coding - Vishal Vashishta