SQL to select rows with the 2 most recent dates-CodePudding

I'm currently struggling with a dataset in which snapshots are saved for multiple companies. That snapshot is performed once a week but is divided over 2 days. I already got the dataset to work with just one recent day with:

Where [TRANSDATE] = (SELECT MAX(TRANSDATE) FROM QBE_INVENTDIMPHYSLOGGING)

But how do I include the 2 most recent dates (always a Saturday and Sunday). So in case of August, that would be 20th of August and the 21st, but I can't use those dates as hard values because it will change every week and I only want to import the most recent weekend. Could anyone assist how to tackle this? Many thanks in advance!

Gr. Diana

CodePudding user response：

On SQL Server, you may use TOP:

SELECT TOP 2 *
FROM QBE_INVENTDIMPHYSLOGGING
ORDER BY TRANSDATE DESC;

If you had to stick with your current subquery approach (which, by the way, is completely fine for finding rank 1 records), you could extend using:

SELECT *
FROM QBE_INVENTDIMPHYSLOGGING
WHERE TRANSDATE = (SELECT MAX(TRANSDATE) FROM QBE_INVENTDIMPHYSLOGGING) OR
      TRANSDATE = (SELECT MAX(TRANSDATE)
                   FROM QBE_INVENTDIMPHYSLOGGING
                   WHERE TRANSDATE < (SELECT MAX(TRANSDATE)
                                      FROM QBE_INVENTDIMPHYSLOGGING));

If you want to capture all ties, then use TOP 2 WITH TIES instead of the first option. You may also use the RANK() analytic function.

CodePudding user response：

You can use

Where [TRANSDATE] IN (SELECT top 2 TRANSDATE FROM QBE_INVENTDIMPHYSLOGGING order by TRANSDATE desc)