Get max date based on another column in SQL-CodePudding

I have a table variable which consists of columns id, date and status as shown below

DECLARE @DateValues TABLE (
    id int identity(1,1), 
    dates datetime,
    status varchar (5)
)

And follows is the sample data

INSERT INTO @DateValues values ('5/22/2021','')
INSERT INTO @DateValues values ('5/21/2021','ABC')
INSERT INTO @DateValues values ('5/22/2021','ABC')

Also declared a variable as shown below.

DECLARE @MaxID INT

From this table I need to get the row which containing the maximum value of date( MAX(dates) ) where status is 'ABC', from the above sample values, I should get the 3rd row as the result and I need to assign the corresponding id value of the row id to a variable (@MaxID).

I tried following queries but getting multiple result set

SELECT id, MAX(dates), Footer
FROM @DateValues
WHERE STATUS = 'ABC'
GROUP BY id, STATUS

SELECT id, dates, status
FROM @DateValues
WHERE dates = (
    SELECT MAX(dates)
    FROM @DateValues
);

I need something like:

@MaxID = id
FROM @DateValues
WHERE dates = (
    SELECT MAX(dates)
    FROM @DateValues
    WHERE STATUS='ABC'
);

Please help.

CodePudding user response：

Is this what you want?

SELECT *
FROM   (
           SELECT *, RN = ROW_NUMBER() OVER (ORDER BY dates DESC)
           FROM   @DateValues
           WHERE  status = 'ABC'           
       ) AS D
WHERE  D.RN = 1