Reputation:
Users:
UserID | UserNick
3 | Joe
23 | Peter
4 | Mary
Messages:
FromUserID | theMSG
3 | Hi
3 | What' up?
23 | asdfg
23 | OK...
4 | Hi, this is Mary
I have a query that gives the following result:
UserID | Message
1 | Hello
1 | How are ya?
2 | yadda yadda
5 | Cool.
5 | I didn't know that.
I now want to limit the result. Not by the number of rows I get back, by the number of different users from whom I want to see the messages.
"Give me three messages of the first 2 users"
UserID | Message
1 | Hello
1 | How are ya?
2 | yadda yadda
But if I write LIMIT 2
, it will give me only
UserID | Message
1 | Hello
1 | How are ya?
How do I achieve what I want? What's the keyword I need?
Upvotes: 1
Views: 892
Reputation: 1963
I think you need to use a nested query like this:
SELECT * FROM messages
WHERE UserID IN
(SELECT DISTINCT UserID FROM messages ORDER BY UserID LIMIT 2)
but without knowing the table structure it is difficult to say more.
The idea is:
Upvotes: 1
Reputation: 3541
You could use the following query. It gives you options to select first n
unique users or last n
unique users. Use ASC
for first or DESC
for last n users.
SELECT *
FROM messages
WHERE UserID IN (
SELECT DISTINCT UserID
FROM messages
ORDER BY UserID ASC/DESC
LIMIT 2
)
LIMIT 2
Upvotes: 0
Reputation: 947
If I understand your question, you can get those results with:
SELECT * FROM table WHERE UserID IN(1, 2)
or
SELECT * FROM table WHERE UserID BETWEEN 1 AND 2
Upvotes: 0