CODEWITHSUNDEEP
databasemysqllimit
joe
joe

Reputation: 35077

how to limit result based on the fields

How to use ' LIMIT' in mysql database query based on fields . just consider a user has 10 phone number and i would like to get only 5 phone number for that user .

I would like to get only 5 phone numbers per user . not only 5 results from database ?

Upvotes: 0

Views: 174

Answers (2)

Pavlo Svirin
Pavlo Svirin

Reputation: 508

You can use "ROW_NUMBER() OVER PARTITION (..)" Mysql simulation for the situation Augenfeind described ( if you make a join and select no more than 5 phone numbers for each user ). See here to understand the logic: http://www.xaprb.com/blog/2005/09/27/simulating-the-sql-row_number-function/

This would be something like:

SELECT * FROM 
users u JOIN
(
select l.user_id, l.phone, count(*) as num
from phones as l
left outer join phones as r
    on l.user_id = r.user_id
    and l.phone >= r.phone
group by l.user_id, l.phone
) p
ON u.user_id=p.user_id AND p.num<=5

Hope that helps.

Upvotes: 1

RSeidelsohn
RSeidelsohn

Reputation: 1178

Hmmm... this should be the same (regarding your last sentence):

SELECT phone_number FROM phone_numbers WHERE user_id = XX LIMIT 5

But if you're looking for a sub-select within another query, then you will have to use a JOIN and can't LIMIT this JOIN. So the way to go would be to first select the users and then - on a per-user-basis - select 5 phone numbers for every user.

Upvotes: 1

Related Questions