0

I am creating a PHP script for maintaining user information, register new user, and login to secure pages. And for that I have a MySQL table user which stores all the information about a user. User table has different fields like userid, username, usergroup_id, email_hash, country etc.

In my script, I want to fetch 10 users from each usergroup_id.
What will be the SQL query for this?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Vinay Jeurkar
  • 3,054
  • 9
  • 37
  • 56
  • What you have tried? Possible duplicate of http://stackoverflow.com/questions/3333665/mysql-rank-function, check it out. – YvesR May 19 '12 at 09:26
  • http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group – sarwar026 May 19 '12 at 09:35
  • you Question is not clear can you share me more information about this scenario – Query Master May 19 '12 at 09:46
  • In user table, usergroup_id field contains values from 1 to 5, which is a unique identifier for identifying usergroups. All i want to fetch from user table is, 10 users from each usergroup. That means 10 users from usergroup_id = 1, 10 users from usergroup_id=2, etc. – Vinay Jeurkar May 19 '12 at 09:58

3 Answers3

1

you can use the LIMIT clause.

Example:

SELECT * FROM `your_table` LIMIT 5, 5

This will show records 6, 7, 8, 9, and 10.

Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results. It is phrased as Limit X, Y and included at the end of your query. X is the starting point (remember the first record is 0) and Y is the duration (how many records to display).

John Woo
  • 258,903
  • 69
  • 498
  • 492
1

I think the following will work for fetching a random set of 10 users for each user group:

select u.*
from user u
where u.userid in (select userid
                   from user
                   where u.usergroupid = user.usergroupid
                   limit 10
                  )

This is using a correlated subquery to limit the row count to 10 for each user group.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Please try this one,

SELECT c.* FROM customers c where c.id in (select c2.id from customers c2 where c.group_id=c2.group_id limit 0, 10)
Dharman
  • 30,962
  • 25
  • 85
  • 135
Pritom
  • 1,294
  • 8
  • 19
  • 37