Get data and union with latest snapshot ❤️ {UPDATED 2023}

I have a project for getting the 3 latest snapshots and union them.

I have a total of 93 snapshots:

For getting the latest snapshot (93), I use this code:

DECLARE @CDP_DATE_CURRENT [varchar](50) = 
    (SELECT MAX([CDP_DATE]) FROM [CD].[dbo].[abc]);

SELECT * FROM [CD].[dbo].[abc]

But how can I get snapshots 92, 91, and 90?

As per the question guide, please do not post images of code, data, error messages, etc. – copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text.

–
1

Will you have to order them, do you have a column that allows you to order them by most recent to least recent? SELECT TOP 3 * FROM CD.dbo.abc ORDER BY CDP_DATE?

–

I don’t quite understand what do you mean by “union them”, but the query below
select the 3 latest snapshots, excluding the last one (as requested):

select *
from (
  select t.*, row_number() over(order by cdp_date desc) as rn
  from cd.dbo.abc t
) x
where rn between 2 and 4;

If you actually want the last three snapshots you can replace the last line by where rn between 1 and 3;.