SQL Query that filters OUT the first and last row

Question

I'm working with SAP Data Services, and have a table that I'm trying to filter. Nothing I can think of in SAP would work, however if I use a SQL query that might.

My questions is: How do I make a query that will filter out the first and last row in a table?

Answer 1

select *
from MY_TABLE
where TRANSACTION_NO > (select min(TRANSACTION_NO) from MY_TABLE)
    and TRANSACTION_NO < (select max(TRANSACTION_NO) from MY_TABLE)

You can substiute whichever column you wish for TRANSACTION_NO (ROW_ID, etc.) to match what constitutes "first" and "last" for your data.

Answer 2

select *
from (
   select t.*, 
          row_number() over (order by ROW_ID) as rn, 
          count(*) over () as total_count
   from the_table
) t 
where rn > 1 and rn < total_count;

If your definition of "first" and "last" is not defined through the ordering of the ROW_ID then you need to adjust the order by ROW_ID in the over(...) clause of the window function.

Answer 3

SELECT ROW_ID, RECORD_TIMESTAMP, TRANSACTION_NO FROM MyTable
EXCEPT
SELECT TOP(1) ROW_ID, RECORD_TIMESTAMP, TRANSACTION_NO FROM MyTable ORDER BY ROW_ID ASC
EXCEPT
SELECT TOP(1) ROW_ID, RECORD_TIMESTAMP, TRANSACTION_NO FROM MyTable ORDER BY ROW_ID DESC