How to select from a column while excluding certain values

Question

In MySQL, is it possible to select two values from a column (with limit 2), excluding certain values? We have a database with inventory, and I want to send an email to users when they have a machine update that is going to take place. However, in this same inventory system, we have the "nobody" (about 10 of them) and "mainconf" (one of them) user, which are not actual users, and won't be receiving an email.

So basically I want to pull 2 values (which are ordered by date, and I know how to do that), which exclude certain values (i.e. "nobody") from being pulled.

So would my query look like this:

SELECT user from data ORDER by checkup_date WHERE data.name != "nobody" AND WHERE data.name != "mainconf" AND WHERE data.name != "testing" ASC limit 2

Please add SHOW CREATE TABLE on the table you're querying. – RolandoMySQLDBA Nov 14 '11 at 20:39 — RolandoMySQLDBA, Nov 14 '11 at 20:39

score 4 · Accepted Answer · answered Nov 14 '11 at 20:40

4

Something like this:

SELECT value1, value2 FROM tablename 
WHERE tablename.name != "nobody" 
AND   tablename.name != "mainconf" LIMIT 2;

answered Nov 14 '11 at 20:40

Hunter McMillen

59,865
24
119
170

score 2 · Answer 2 · answered Nov 14 '11 at 21:06

Your query is close but don't repeat the WHERE and put the ORDER after the WHERE :

SELECT user from data WHERE name <> "nobody" AND name <> "mainconf" AND name <> "testing" ORDER by checkup_date ASC limit 2

Or :

SELECT user from data WHERE name not in ("nobody" ,"mainconf", "testing") ORDER by checkup_date ASC limit 2

score 0 · Answer 3 · answered Jan 10 '21 at 12:06

0

I would like to suggest a better answer

SELECT user  FROM data
WHERE name NOT IN('mainconf','testing') LIMIT 2

This will return all valuer that their name are not mainconf or testing

works in MySQL versions higher than 5.6

answered Jan 10 '21 at 12:06

DavSev

1,005
4
22
47

score 0 · Answer 4 · answered Nov 14 '11 at 21:06

You could try something like this:

SELECT B.* FROM
(SELECT X.id
FROM
    (SELECT id FROM inventory
    WHERE username IN ('nobody','mainconf')
    ORDER BY date DESC LIMIT 2) X
    LEFT JOIN
    (SELECT id FROM inventory
    WHERE username IN ('nobody','mainconf')) Y
    USING (id) WHERE Y.id IS NULL
) A
INNER JOIN inventory B USING (id);

You must make sure the inventory table has the right indexes to support the subqueries:

ALTER TABLE inventory ADD INDEX date_id_ndx (date,id);
ALTER TABLE inventory ADD INDEX username_id_ndx (username,id);
ALTER TABLE inventory ADD INDEX username_date_id_ndx (username,date,id);

The LEFT JOIN is All Keys Ordered By Date LIMIT 2 against All Keys for what is to be excluded.

Give it a Try !!!

This query would attempt to get just two keys for subquery 'A'. Then, an INNER JOIN of two keys to the main table. I have done this before : http://stackoverflow.com/questions/5983156/fetching-a-single-row-from-join-table/6023217#6023217 — RolandoMySQLDBA, Nov 14 '11 at 21:18

How to select from a column while excluding certain values

4 Answers4