MySQL select where not in another returned data from sql statement-CodePudding

I have this problem where I want to first select 8 elements from a mysql database ordering by id DESC. Then I want to select another group of results (8 items), this time order by date DESC but the results here I want to ensure that they are not already on the fisrt query the one for ordering by id. The data is in the same table just with different columns like id,name,date,.

So far I have tried writing different queries to get the data but the data contains some similar items of which that is what I don't want. Here are the queries I have written;

this returns 8 items sorted by id DESC

SELECT name FROM person order by id DESC LIMIT 8;

this returns 8 items also but sorted by date DESC

SELECT name FROM person order by date DESC LIMIT 8;

the returned data contain duplicate items!

CodePudding user response：

The first query should return the primary key for the table. If name is the key then so be it, but probably that id field is the better choice.

Then we can write the query like this:

SELECT p.name 
FROM Person p
WHERE NOT EXISTS (
    SELECT 1
    FROM (SELECT id FROM Person ORDER BY id DESC LIMIT 8) p0
    WHERE p0.id = p.id
)
ORDER BY p.date DESC 
LIMIT 8;

We could also use an exclusion join which is usually slower, but in this case reduces one level of nesting so it might do better:

SELECT p.name
FROM Person p
LEFT JOIN (
    SELECT id 
    FROM Person 
    ORDER BY id DESC 
    LIMIT 8
) p0 ON p0.id = p.id
WHERE p0.id is null
ORDER BY p.date DESC
LIMIT 8;

One other thing to keep in mind is MySQL is strict about what kinds of subquery can use the LIMIT keyword. Specifically, you need it to be a derived table. I know the exclusion join option should qualify, but I'm less sure of the NOT EXISTS() option.

CodePudding user response：

You could use a nested query, first select the first 8 id's, then select the first 8 records ordered by date, excluding those id's:

SELECT name FROM person 
WHERE id NOT IN
  (SELECT id FROM person order by id DESC LIMIT 8) AS exc
ORDER BY date DESC LIMIT 8