Get the 3rd largest time value in SQL Server 2018-CodePudding

I want to get the 1st, 2nd, 3rd,...largest value of Time in SQL Server 2018's table.

In my code when I get the 3rd largest, if my month condition is 5, it returns correct value. But if my month condition is 4, it returns the max time value not my expected time value ( 3rd ). I don't know why.

My query:

SELECT MAX([Time1].[Time]) FROM [dbo].[index4_MonthChart] as [Time1] 
WHERE 
Month([Time])  = '4'
AND [Time1].[Time] < ( SELECT MAX([Time2].[Time]) FROM [dbo].[index4_MonthChart]  as [Time2] 
WHERE 
[Time2].[Time] < ( SELECT MAX([Time3].[Time]) FROM [dbo].[index4_MonthChart] as [Time3]))

All my value in table:

When month(time) condition is 5, it returns:

But when month(time) is 4, it always returns the max time value and it is wrong ( The correct time value have to be 2023-04-28 ) :

CodePudding user response：

Very simple. You need to add a where clause in your sub-selects to restrict those to Month= 4 as well. Since there are at least two times greater than 30.04 (IE all the times from May), your current query returns the maximum time for April. This doesn't happen for May, because your data has no values later than May.