Clever Geek Handbook

SQL: select rows that were not selected in the previous query

SELECT * FROM table ORDER BY id ASC

Will display the complete table:

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

Order by weight and limited to 3:

SELECT * FROM table ORDER BY weight DESC LIMIT 3

The output will be:

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

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

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

I just couldn't figure out how to do it. I am using PHP and Mysqli if that helps. Thanks to

+3


source to share


2 answers 


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


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

+1


source


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 lines 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;
0


source






More articles:


All Articles