CODEWITHSUNDEEP
mysqlsql
methuselah
methuselah

Reputation: 13206

SELECT to Skip First and Last Rows and Select in Between

I'm trying to to skip the first and last rows in my SQL query (i.e. skip where sequence=0 and sequence=4 in this case) but my SQL query does not seem to be working. Any idea why? The logic seems correct:

SELECT * FROM waypoint 
WHERE id NOT IN(
        (SELECT MIN(ID) FROM waypoint), 
        (SELECT MAX(ID) FROM waypoint)
      )
AND booking_id="1";

MY SQL FIDDLE IS HERE

Upvotes: 2

Views: 1262

Answers (3)

Thallius
Thallius

Reputation: 2619

I dont know if it works and I cannot test it here in the moment but have you tried

SELECT * FROM waypoint WHERE booking_id="1" ORDER BY ID LIMIT 2,COUNT(ID)

This could be more perfoment because you dont need two subselects.

Upvotes: 0

Simulant
Simulant

Reputation: 20112

you need to copy your second part of your where clause into the select of your not in clause, because each select is handled for its own and has access to the whole dataset. So you need to add the restriction logic to each select. Otherwise your MAX(ID) will return 7 because it has access to the whole dataset:

SELECT * FROM waypoint 
WHERE id NOT IN(
        (SELECT MIN(ID) FROM waypoint where booking_id="1"), 
        (SELECT MAX(ID) FROM waypoint where booking_id="1")
      )
AND booking_id="1";

Upvotes: 0

rtruszk
rtruszk

Reputation: 3922

You have 7 rows. Their id's are from 1 to 7. Your not in clause filters out 1 and 7. If you want to skip first and last with booking_id=1 you should add this clause to subselects: 

SELECT * FROM waypoint 
WHERE id NOT IN(
        (SELECT MIN(ID) FROM waypoint where booking_id="1"), 
        (SELECT MAX(ID) FROM waypoint where booking_id="1")
      )
AND booking_id="1";

Upvotes: 3

Related Questions