Clever Geek Handbook

Get the second highest sample date

I am trying to get the second highest sampled date and get the following error please let me know where I am doing it wrong

(SELECT* FROM resheader WHERE sampledate =
   (SELECT MAX(sampleDate) FROM resheader 
      WHERE sampleDate < (SELECT MAX(sampleDate) FROM resheader)
   )
) as 'Previous Sample'

Mistake

Msg 116, Level 16, State 1, Line 12 Only one expression can be specified in the select list when no subquery is entered with EXIST.

+3


source to share


3 answers


In SQL Server 2012, you can use a clause OFFSET FETCH

to get the second row (after sorting by your desired criteria):



SELECT * FROM resheader 
ORDER BY sampledate DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY
+1


source


How about this? Assuming all dates are unique:

SELECT rh.*
FROM resheader rh
ORDER BY sampleDate DESC
LIMIT 1, 1;


If they are not unique, then:

SELECT rh.*
FROM resheader rh
WHERE sampledate = (SELECT DISTINCT SampleDate
                    FROM resheader
                    ORDER BY sampleDate DESC
                    LIMIT 1, 1
                   );
0


source


If your version of SQL Server does not support FETCH OFFSET

(2012 and above). You can use ranking functions to achieve this:

SELECT T.sampledate 
FROM (
    SELECT DENSE_RANK() OVER (ORDER BY sampledate DESC) AS RN, *
    FROM resheader
    ) AS T
WHERE T.RN = 2;
0


source






More articles:


All Articles