Getting the second record using EXCEPT

Question

I want to get the second record from bottom. I tried

SELECT TOP 2 meetings_id FROM meetings ORDER BY meetings_date DESC 
EXCEPT
SELECT TOP 1 meetings_id FROM meetings ORDER BY meetings_date DESC

But there is syntax error in except.
When I try to delete order by it works but that giving me the second record from the top.

Answer 1

Try using OFFSET and FETCH

select meetings_id from meetings ORDER BY meetings_date DESC 
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY

OFFSET

FETCH

Answer 2

This solution only works while you have > 1 rows in your table.

SELECT TOP 1 meetings_id FROM
(SELECT TOP 2 meetings_id, meetings_date FROM meetings 
 ORDER BY meetings_date DESC) x                     
ORDER BY meetings_date;

Based on answer from this link.

You'll probably get an error or empty result since there is no penultimate row. So your query will be faulty in this case.

Answer 3

If you really want to use EXCEPT because of some strange requirement or trivia, then you need to put the TOP queries into table expressions:

SELECT meetings_id FROM 
(
  SELECT TOP (2) meetings_id 
    FROM dbo.meetings 
    ORDER BY meetings_date DESC 
) AS bottom2
EXCEPT
SELECT meetings_id FROM
(
  SELECT TOP (1) meetings_id
    FROM dbo.meetings 
    ORDER BY meetings_date DESC
) AS bottom1;

But this is neither intuitive nor efficient.

• Examples: db<>fiddle
• With showplan comparisons, showing why EXCEPT is the worse solution offered thus far: db<>fiddle