Select record with most recent sequence count-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

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.