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
user5161329
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
Salman A
source
to share
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
Gordon linoff
source
to share
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
Evaldas Buinauskas
source
to share