SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast sabret00the's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    grouping by for a query

    is their a method at all to get it only select XX records from each group?

    this is the query
    PHP Code:
                    SELECT grps.groupidgrps.titlegrps.descriptiongrps.catidgrps_catergories.title AS catnamegrps.approvedgrps.leaderid 
                      FROM grps 
                     LEFT JOIN grps_catergories ON 
    (grps_catergories.catid grps.catid
                      
    WHERE grps.approved=
                       GROUP BY grps_catergories
    .catid
                      ORDER BY grps
    .title 
    and i'd like to select a maximum of 10 records from each catid

    and example of the catid table would be
    Code:
     a table (catergories)
       catid   	  title   	  description
       1 	Automotive 	For groups referring to cars, bikes or such
       2 	Computers 	For groups referring to computers, whether hardwar...
       3 	Education 	For groups regarding learning, whether it be help ...
       4 	Entertainment 	For groups surrounding anything entertaining, visu...
       5 	Games 	For groups surrounding games you love, digital or ...
       6 	Health & Fitness 	For groups pertaining to well-being
       7 	Home & Families 	For groups regarding the home life
       8 	Lifestyles 	For groups surrounding lifestyles whether it be go...
       9 	Places & Travel 	For groups referring to towns, cities and holidays
       10 	Religion & Belief 	For groups surrounding christianity, islam, buddis...
     11 	Sports & Recreation 	For groups referring to sports, whether it be Foot...
       12 	Others 	For everything else
    these are leftjoined to a groups table so it will display all groups that fall into each catergory, however i went to make it so it shows a maximum of ten records for each catid

    i'm wondering if this is possible via MySQL?

  2. #2
    SitePoint Guru coiL's Avatar
    Join Date
    Sep 2001
    Location
    QLD, Australia
    Posts
    666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I understand correctly what you are asking, you need to use LIMIT

    PHP Code:
    SELECT grps.groupidgrps.titlegrps.descriptiongrps.catidgrps_catergories.title AS catnamegrps.approvedgrps.leaderid 
                      FROM grps 
                    LEFT JOIN grps_catergories ON 
    (grps_catergories.catid grps.catid
                      
    WHERE grps.approved=
                       GROUP BY grps_catergories
    .catid
                      ORDER BY grps
    .title 
                       LIMIT 10 
    coiL
    "cradled in the learning curve"

  3. #3
    SitePoint Enthusiast sabret00the's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    limit would limit it to ten records in total where as i want to limit it to ten records per grps_catergories.catid.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, coil, those would the the first 10 titles overall

    snaggle, er, i mean sabret00the wants 10 per category

    something like this --
    Code:
    select C.title      as catname
         , G1.title     as groupname
         , G1.description
         , G1.leaderid 
      from grps_catergories as C
    inner
      join grps as G1
        on C.catid = G1.catid
       and G1.approved = 1 
    inner
      join grps as G2
        on C.catid = G2.catid
       and G2.approved = 1 
       and G1.title >= G2.title
    group 
        by C.title    
         , G1.title   
         , G1.description
         , G1.leaderid 
    having count(*) <= 10     
    order 
        by G.title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast sabret00the's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks rudy i'll try this out


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •