CODEWITHSUNDEEP
mysql
MatthewSot
MatthewSot

Reputation: 3594

MySQL Limit by OR

I was wondering if there's a way in MySQL to, say, LIMIT 1 for each OR. As in, if I have a MySQL statement SELECT * FROM posts WHERE username = 'Johnny' OR username = 'Matthew' OR username = 'Andy' OR username = 'Sandra' ORDER BY date DESC but I want to return only one result from each username (or, in this case, the latest post from each person). I've tried SELECT * FROM posts WHERE (username = 'Johnny' LIMIT 1) OR (username = 'Matthew' LIMIT 1) OR (username = 'Andy' LIMIT 1) OR (username = 'Sandra' LIMIT 1) ORDER BY date DESC but that's about all I could think of (and it didn't work). Thanks in advance, Matthew

Upvotes: 0

Views: 111

Answers (2)

eggyal
eggyal

Reputation: 125855

You can use UNION to combine separate queries:

(SELECT * FROM posts WHERE username = 'Johnny'  LIMIT 1)
UNION ALL
(SELECT * FROM posts WHERE username = 'Matthew' LIMIT 1)
UNION ALL
(SELECT * FROM posts WHERE username = 'Andy'    LIMIT 1)
UNION ALL
(SELECT * FROM posts WHERE username = 'Sandra'  LIMIT 1)

Alternatively, you can group the results by username:

SELECT   *
FROM     posts
WHERE    username IN ('Johnny','Matthew','Andy','Sandra')
GROUP BY username

Both of these queries as given above will return indeterminate rows for each user (which may or may not be what you need). To fetch specific rows, please specify how MySQL can determine which rows you want.

UPDATE

To obtain the most recent posts for each user and order the resultset from newest to oldest:

(SELECT * FROM posts WHERE username = 'Johnny'  ORDER BY date DESC LIMIT 1)
UNION ALL
(SELECT * FROM posts WHERE username = 'Matthew' ORDER BY date DESC LIMIT 1)
UNION ALL
(SELECT * FROM posts WHERE username = 'Andy'    ORDER BY date DESC LIMIT 1)
UNION ALL
(SELECT * FROM posts WHERE username = 'Sandra'  ORDER BY date DESC LIMIT 1)
ORDER BY date DESC

And:

SELECT   *
FROM     posts NATURAL JOIN (
           SELECT   username, MAX(date) AS date
           FROM     posts
           WHERE    username IN ('Johnny','Matthew','Andy','Sandra')
           GROUP BY username
         ) AS t
GROUP BY username -- only required if multiple posts might have the same date
ORDER BY date DESC

Upvotes: 2

sel
sel

Reputation: 4957

How about using union?

Edited with thanks to @eggyal correction:

(SELECT * FROM posts WHERE username = 'Johnny' LIMIT 1)
UNION
(SELECT * FROM posts WHERE username = 'Matthew' LIMIT 1)
UNION
(SELECT * FROM posts WHERE username = 'Andy' LIMIT 1)

Upvotes: 0

Related Questions