Return column with the second largest date in the dataset-CodePudding

I have a simple query that returns the dataset and a measure that represents a column with the highest date found.

select *, max(date) over () as date_max
from table

I need to increment this query and insert a new column that returns the second highest date found (it will still contain that column with the highest date). Any idea how to do this?

CodePudding user response：

You can just query for the second largest like

select max(date) from table where date < (select max(date) from table)

You can use this in a subquery in the main query for example. Alternatively, you can get the max date first, and then use it to get the second largest value, also using a window function like you did, like so:

with top1 as 
(
select i, max(i) over () as largest
  from d
)
select i, largest
       -- ignore the largest value when aggregating
     , max(case when i = largest then null else i end) over () as second_largest
  from top1

Keep in mind both of them will return the actual second largest value, duplicates excluded (so 100, 100, 99 would return 99 as the second largest).

live demo on dbfiddle