How to extract info based on the latest ID

Question

I have a table that looks like this:-

TABLEA

SERIAL  SKU GRADE   ID
HA501   R2022   2   2011063
HA501   R2022   1   2011052
HA502   R2033   2   2011051
HA502   R2033   3   2011048
HA503   R2044   1   2011034
HA503   R2044   2   2011023

I wish to extract the above SERIAL, SKU & GRADE based on the latest ID, like this:

FINAL OUTPUT            
SERIAL  SKU GRADE   ID
HA501   R2022   2   2011063
HA502   R2033   2   2011051
HA503   R2044   1   2011034

I was trying to using SQL:

select 
     SERIAL, SKU, GRADE, MAX(ID) 
from tableA
group by SERIAL, SKU, GRADE

but somehow the output doesn't seems right.

Can someone help me ?

Thanks.

**WHAT** database and which version?? **SQL** is just the Structured Query Language - a language used by **many** database systems - **SQL** is **NOT** a database product...... — marc_s, Aug 13 '11 at 13:19
@marc, could you enlighten me how this query would change if he would have said mysql or oracle? Know-it-all people I swear... — Blindy, Aug 13 '11 at 19:10
@Blindy: as far as I know (no expert on those!), neither Oracle nor MySQL have this CTE (Common Table Expression) feature, so my solution would not work in neither MySQL nor Oracle (you'd have to use some other approach) — marc_s, Aug 13 '11 at 19:25

score 2 · Answer 1 · answered Aug 13 '11 at 12:55

2

Try this:

SELECT *
  FROM tableA
WHERE id IN 
(
    SELECT MAX(id) 
            FROM tableA
    GROUP BY serial, sku
)

This can be improved based on specific database type/version, if you update your question with same.

answered Aug 13 '11 at 12:55

Chandu

81,493
19
133
134

marc_s · Accepted Answer · 2011-08-13T15:26:00.590

1

Should you be using SQL Server 2005 and newer, you could use a CTE with the ROW_NUMBER construct:

;WITH DataForLastID AS
(
    SELECT 
        SERIAL, SKU, GRADE, ID,
        ROW_NUMBER() OVER(PARTITION BY SERIAL, SKU ORDER BY ID DESC) AS 'RowNum'
    FROM
       dbo.TABLEA
)
SELECT
    SERIAL, SKU, GRADE, ID
FROM
   DataForLastID
WHERE
    RowNum = 1

This will "partition" your data by Serial,SKU,Grade and for each group of the same values, it will sequentially number the rows starting at 1, ordered by descending ID (so the largest/newest ID will be RowNum = 1).

edited Aug 13 '11 at 15:26

answered Aug 13 '11 at 13:23

marc_s

732,580
175
1,330
1,459

Tried that but it doesn't extract the newest records. In fact, it extracted all records. – Bob Aug 13 '11 at 14:53
@Bob: sorry, there was an error in the `PARTITION BY` clause - fixed it, now the results are as expected. Please try again and see for yourself – marc_s Aug 13 '11 at 15:26

Derek · Answer 3 · 2011-08-13T15:19:20.233

You're not getting the desired results because GRADE is part of the grouping. Take a look at the data:

SERIAL  SKU GRADE   ID
HA501   R2022   2   2011063
HA501   R2022   1   2011052
HA502   R2033   2   2011051
HA502   R2033   3   2011048
HA503   R2044   1   2011034
HA503   R2044   2   2011023

You're expecting to get back the following row for SERIAL = HA501:

HA501   R2022   2   2011063

However, there are multiple GRADE values for that SERIAL/SKU. Therefore, you'll need to remove the GRADE from the GROUP BY, otherwise you'll get the MAX(ID) for each combination of SERIAL, SKU, GRADE (which, in this case, would result in basically every row being returned).

So, the correct query will look like this:

SELECT SERIAL, SKU, MAX(id) as MaxID
FROM TABLEA
GROUP BY SERIAL, SKU

However, that won't include the GRADE column. If you need the corresponding GRADE returned as well, you'll need to do this (as Cybernate has already shown):

SELECT SERIAL, SKU, GRADE, ID
FROM TABLEA
WHERE ID IN (SELECT MAX(ID) FROM TABLEA GROUP BY SERIAL, SKU)

Which gets a list of the maximum IDs we established in the previous query and returns all data matching those IDs.

Alternatively, try this:

SELECT a.SERIAL, a.SKU, a.GRADE, a.ID
FROM TABLEA a
INNER JOIN (
   SELECT SERIAL, SKU, MAX(ID) as ID FROM TABLEA GROUP BY SERIAL, SKU) b
ON A.SERIAL = B.SERIAL AND A.SKU = B.SKU AND A.ID = B.ID

Thanks, but when I tried it out in Ms SQL Server 2005 (version 9), it doesn't seems to work ie. no error when I executed it but it just keep running till I stopped it. — Bob, Aug 13 '11 at 14:36
Give the query at the bottom a try. Also, try executing the subquery (SELECT SERIAL, SKU, MAX(ID) as ID FROM TABLEA GROUP BY SERIAL, SKU) on its own and see if that returns in a reasonable time. — Derek, Aug 13 '11 at 15:20
For a large table in SQL Server 2005, Derek's last query should give you the best performance results. — John N, Aug 13 '11 at 17:15

score 0 · Answer 4 · answered Aug 13 '11 at 18:32

The In Group By has overhead that is not necessary. It will be evaluated each loop and you don't need the MAX for all for each loop. If ID is not unique across SERIAL and SKU could cause undesired output

   SELECT TA1.SERIAL, TA1.SKU, TA1.GRADE, TA1.ID
   FROM TABLEA AS TA1
   WHERE TA1.ID = ( SELECT MAX(TA2.ID) 
                    FROM TABLEA AS TA2 
                    WHERE TA2.SERIAL = TA1.SERIAL 
                      AND TA2.SKU = TA1.SKU ) 
   ORDER BY TA1.SERIAL, TA1.SK

How to extract info based on the latest ID

4 Answers4