Last of max 2 dates

Question

Hi,
I wanted to find out last max 2 dates from table but unable to do so

i tried

CREATE TABLE [dbo].[Table_1](

[div_id] nchar NULL,

[customerid] nchar NULL,

[div_status] nchar NULL,

[logdate] [date] NULL

) ON [PRIMARY]

GO

Insert Data:

INSERT INTO [dbo].[Table_1]

([div_id]

,[customerid]

,[div_status]

,[logdate])

VALUES

('495','-1','1','2021-02-14'),

('495','-1' ,'1','2021-02-14'),

('495','-1' ,'1','2021-02-14'),

('502' ,'-1', '1','2021-02-14'),

('513', '-1','1','2021-02-14'),

('538', '-1','1','2021-02-14'),

('545', '-1','1','2021-02-14'),

('563', '-1','1','2021-02-14'),

('563', '-1','1','2021-02-13'),

('577', '-1','1','2021-02-12'),

('577', '-1','1','2021-02-10'),

('577', '-1','1','2021-02-14');

I tried

SELECT TOP(2) * FROM TABLE_1 WHERE LOGDATE<(SELECT MAX(LOGDATE) FROM TABLE_1)

but i do not want to add top(2) condition to get data like above i mentioned .Please help me with any other condition for previous date

Answer 1

If you want last 2 then there is no way to get around a TOP 2 logic as that is exactly what it is doing. If you want the max date then MAX(LogDate) gives you that. If you want the max 2 dates then you'd combine the two but note that ordering is irrelevant without an ORDER BY clause. Without an explicit ordering column(s) then the ordering is undefined.

Note: I'm using a table variable @Table_1. Replace with your actual table name.

This gives you the top 2 unique LogDate values. In your sample data it would be 2021-02-14 and 2021-02-13.

SELECT TOP 2 Max(LogDate) FROM @Table_1 GROUP BY LogDate ORDER BY LogDate DESC

If you want the top 2 rows for the max LogDate then the query is a little different. This query gives you the top 2 rows where the rows are ordered by LogDate descending and then by the div_id ascending. The results here would be 495 in both cases because you have 2 rows with the same values.

SELECT TOP 2 div_id FROM @Table_1 ORDER BY LogDate DESC, div_id

Answer 2

In the logdate column - i am trying to use this last max 2 days condition without affecting all the data which is already coming

SELECT Div_ID,

CustomerID,

Div_Status,

LogDate ,

LogDate

FROM ( SELECT Div_ID,

CustomerID,

Div_Status,

LogDate,

LAG(Div_Status,1,0) OVER (PARTITION BY Div_ID ORDER BY logdate) AS PreviousValue,

LEAD (Div_Status,0,1) OVER (PARTITION BY Div_ID ORDER BY logdate) as NextValue

FROM dbo.Table_1

) AS t

WHERE (PreviousValue = 0 AND Div_Status = 1 AND NextValue = 1 ) OR

(PreviousValue = 0 AND Div_Status = 0 AND NextValue = 1 ) OR

(PreviousValue = 0 AND Div_Status = 1 AND NextValue = 0 ) OR

(PreviousValue = 1 AND Div_Status = 0 AND NextValue = 0 )

Answer 3

Hi @Shambhu Rai

SELECT TOP(2) * FROM TABLE_1 WHERE LOGDATE<(SELECT MAX(LOGDATE) FROM TABLE_1)

How can you use TOP(2) without ORDER BY clause??

without affecting all the data which is already coming

Maybe like this:

    ;WITH CTE AS  
    (  
     PUT Your code here  
    )  
    SELECT TOP 2 ......   FROM  CTE  
    ORDER BY ......  

If you don't want to use TOP ,you can have a try on OFFST FETCH .
Please refer to this document for more details : Using OFFSET and FETCH to limit the rows returned

Best regards,
LiHong