How to get MAX ID while grouping on specific column?

user_9188324 0
Viewed 354

I have this table structure and sample data. In this table there is sale order's iterations. At first Mouse was requested for 15 Qty. Later on buyer decides to update the Qty and make it 25 and then again later he realizes he only needs 20. So I want to get the most latest row for specific Item from sale order.

CREATE TABLE SaleOrder
(
ID INT PRIMARY KEY,
Item VARCHAR(25),
Qty INT
)

INSERT INTO SaleOrder VALUES (1, 'Mouse', 15);
INSERT INTO SaleOrder VALUES (2, 'Key Board', 10);
INSERT INTO SaleOrder VALUES (3, 'Printer', 3);
INSERT INTO SaleOrder VALUES (4, 'Mouse', 25);
INSERT INTO SaleOrder VALUES (5, 'Mouse', 20);

Here is the query I have wrote.

SELECT ID, Item, Qty
FROM SaleOrder M
WHERE ID = (SELECT MAX(ID) FROM SaleOrder S WHERE S.ID = M.ID GROUP BY Item)

The expected out put should contain 3 rows like this.

ID  Item       Qty
1   Mouse      15
2   Key Board  10
3   Printer    3
4   Mouse      25
5   Mouse      20

Is there something I am missing here ?

sql-server sql
3 Answers

You can try to use ROW_NUMBER window function to get max one by each Item

SELECT ID,Item,Qty
FROM (
   SELECT *,ROW_NUMBER() OVER(PARTITION BY Item ORDER BY ID DESC) rn
   FROM SaleOrder
) t1
where rn = 1

another simple way we can try to use TOP 1 WITH TIES without a subquery.

SELECT TOP 1 WITH TIES ID,Item,Qty
FROM SaleOrder 
ORDER BY ROW_NUMBER() OVER(PARTITION BY Item ORDER BY ID DESC)

SQLfiddle

user_51760710

You May Use Either of the following Methods :

Using ROW_NUMBER()

;WITH CTE
AS
(
    SELECT
        RN = ROW_NUMBER() OVER(PARTITION BY Item ORDER BY Id DESC),
        *
        FROM SaleOrder
)
SELECT
    *
    FROM CTE
        WHERE RN = 1

Using Sub Query

SELECT
    *
    FROM SaleOrder SO
        WHERE ID = (
            SELECT MAX(ID) FROM SaleOrder WHERE Item = SO.Item
        )-- You Don't Need the Group By Here

Result 

ID          Item                      Qty
----------- ------------------------- -----------
3           Printer                   3
5           Mouse                     20
2           Key Board                 10
user_51061540

You can try the following query using MAX.

SELECT SaleOrder.* FROM (
SELECT MAX(Id) as Id, Item FROM SaleOrder Group By Item
)a INNER JOIN SaleOrder ON a.Id = SaleOrder.Id

OR you can also use the following

SELECT *
FROM SaleOrder D
WHERE Id = (SELECT MAX(Id) FROM SaleOrder WHERE Item = D.Item)

The output in both case is as shown below

ID  Item        Qty
--------------------
3   Printer      3
5   Mouse       20
2   Key Board   10

You can see the demo here- Group By Max Demo

user_105325000
Related