CODEWITHSUNDEEP
mysqlsql
idoen
idoen

Reputation: 53

Select rows that were not selected in a previous query

SELECT * FROM table ORDER BY id ASC

Will output the full table:

id name weight
-- ---- ------
1  XXL 450
2  L   20
3  XL  30
4  XXL 875
5  S   2

Ordering by the weight and limiting to 3:

SELECT * FROM table ORDER BY weight DESC LIMIT 3

Will output:

id name weight
-- ---- ------
4  XXL 875
1  XXL 450
3  XL  30

I want to select all the rows that were not selected in the last query, as such:

id name weight
-- ---- ------
2  L   20
5  S   2

Upvotes: 3

Views: 662

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would just do:

select t.*
from t
where id not in (select id from t order by weight desc limit 3);

or:

select t.*
from t left join
     (select id from t order by weight desc limit 3) tt
     on t.id = tt.id
where tt.id is null;

However, you need to be very careful about what happens when two rows have the same weight. So, I would recommend these two queries:

select id
from t
order by weight desc, id
limit 3

select t.*
from t left join
     (select id from t order by weight desc, id limit 3) tt
     on t.id = tt.id
where tt.id is null;

Upvotes: 1

Ilario Pierbattista
Ilario Pierbattista

Reputation: 3265

SELECT T.* FROM (    
    SELECT * FROM table ORDER BY weight DESC LIMIT 10 OFFSET 3
) AS T ORDER BY T.id

The inner query order the rows by weight and crop the results starting from the 4th row to the 13th. The outer query order the partial result by id.

Upvotes: 1

Related Questions