Select record with most recent sequence count

user_19558196 0
Viewed 24

I have only been playing around with SQL for a couple of weeks, and very casually at that. So this is probably a super simple question. For ease of conveying it i have limited the script i have to only relevant data

SELECT
  ASSET_MAIN_NUMBER,
  FISCAL_YEAR_PERIOD,
  SEQUENCE_NUMBER_IN_THE_PERIOD
FROM
  valuetable    
WHERE
  assetnumber = 123
  AND FISCAL_YEAR_PERIOD = 2021003

the assetnumber will have multiple records within 2021003, noted by the sequence number field (1, 2, 3, 4, etc). How can i limit this to only return the highest numerical value from the sequence field? The values wont always be in order either, as with this example i have records for 01, 03, and 04.

Thank you!

sql
2 Answers

Depending on your RDBMS (sql server, mysql, oracle, postgres, etc) you will want to use TOP or LIMIT and ORDER BY keyword.

Using TOP:

SELECT TOP 1   
  ASSET_MAIN_NUMBER,
  FISCAL_YEAR_PERIOD,
  SEQUENCE_NUMBER_IN_THE_PERIOD 
FROM valuetable 
WHERE
  assetnumber = 123
  AND FISCAL_YEAR_PERIOD = 2021003 
ORDER BY SEQUENCE_NUMBER_IN_THE_PERIOD DESC

Using LIMIT:

SELECT
  ASSET_MAIN_NUMBER,
  FISCAL_YEAR_PERIOD,
  SEQUENCE_NUMBER_IN_THE_PERIOD 
FROM valuetable 
WHERE
  assetnumber = 123
  AND FISCAL_YEAR_PERIOD = 2021003 
ORDER BY SEQUENCE_NUMBER_IN_THE_PERIOD DESC
LIMIT 1
user_22210010

what if i want to then remove the filter on asset number/fiscal and always return the top 1 record per the sequence level , if that makes sense

You can use a windowing function to do that -- like this:

SELECT
  ASSET_MAIN_NUMBER,
  FISCAL_YEAR_PERIOD,
  SEQUENCE_NUMBER_IN_THE_PERIOD
FROM (
  SELECT
    ASSET_MAIN_NUMBER,
    FISCAL_YEAR_PERIOD,
    SEQUENCE_NUMBER_IN_THE_PERIOD,
    ROW_NUMBER() OVER (PARTITION BY FISCAL_YEAR_PERIOD ORDER BY SEQUENCE_NUMBER_IN_THE_PERIOD DESC) RN
  FROM valuetable    
  WHERE assetnumber = 123 
) X 
WHERE RN = 1

Because ROW_NUMBER() is partitioned by the fiscal the first one will always be the greatest -- we just get all the records where it equals 1.

user_2157520
Related