how to limit result based on the fields

Question

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 ?

Answer 1

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.

Answer 2

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%5Fnumber-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.