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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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.