Get data and union with latest snapshot

Question

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?

Answer 1

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;.