CODEWITHSUNDEEP
mysql
Steven Matthews
Steven Matthews

Reputation: 11275

How to select from a column while excluding certain values

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

Upvotes: 2

Views: 10120

Answers (4)

DavSev
DavSev

Reputation: 1111

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

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44333

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 !!!

Upvotes: 0

gregory
gregory

Reputation: 836

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

Upvotes: 3

Hunter McMillen
Hunter McMillen

Reputation: 61515

Something like this:

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

Upvotes: 4

Related Questions