CODEWITHSUNDEEP
mysqlsql
RyuAkamatsu
RyuAkamatsu

Reputation: 239

Excluding first and last result from sql query

I'm fairly new to SQL so this may be fairly simple but I'm trying to write a script in SQL that will allow me to get a set of data but I don't want the first or last result in the query. I can find lots on how to remove the first result and how to remove the last result but not both.

This is my query so far:

SELECT * FROM itinerary Where ID = 'A1234' ORDER BY DateTime ASC

I want to remove the first and the last record of that select based on the DateTime.

Upvotes: 2

Views: 3815

Answers (3)

PaulF
PaulF

Reputation: 6773

Select in reverse order & skip first and then select in the required order from the result, skipping first. 

SELECT * FROM (SELECT *
FROM itinerary 
Where ID = 'A1234' 
ORDER BY DateTime DESC
LIMIT 1, 18446744073709551615) x ORDER BY DateTime ASC
LIMIT 1, 18446744073709551615

18446744073709551615 is max integer just in case you wanted to know why I picked that value

Upvotes: 0

anwaar_hell
anwaar_hell

Reputation: 776

Try this ..

select * from
(select a.*,row_number() over (partition by DateTime order by DateTime desc) as rnm
from itinerary Where ID = 'A1234')x
where rm <> 1 and rm not in ( 

select max(rm) from
(
select row_number() over (partition by DateTime order by DateTime desc) as rnm
from itinerary Where ID = 'A1234'))

Upvotes: 0

Jim
Jim

Reputation: 6881

This may not be the most performant way to do this, but you didn't give any schema info, and it looks like your ID column is not unique. It would be easier if you had a primary key to work with.

SELECT * FROM itinerary 
WHERE ID = 'A1234' 
AND DateTime <
  (SELECT MAX(DateTime) FROM itinerary WHERE ID = 'A1234')
AND DateTime >
  (SELECT MIN(DateTime) FROM itinerary WHERE ID = 'A1234')
ORDER BY DateTime ASC

This will basically select every record where the ID is A1234 and the DateTime doesn't equal the max or min datetime. Please note, if you have multiple records with the same value for DateTime and that also happens to be the min or max value, you might exclude more than just the first or last.

This might be good enough though. If not, you might need to write a stored procedure and not just straight ANSI SQL.

Upvotes: 2

Related Questions