CODEWITHSUNDEEP
mysql
user1598019
user1598019

Reputation:

SQL LIMIT expression

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

Answers (3)

Zagor23
Zagor23

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:

  • get the users you want in nested query
  • get their messages with outer query

Upvotes: 1

Ghazanfar Mir
Ghazanfar Mir

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
  1. Inner Limit defines the number of unique users you want to select.
  2. Outer Limit definesthe number of messages you wish to see from users

Upvotes: 0

Jessai
Jessai

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

Related Questions