SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Sep 2008
    Posts
    341
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL GROUP BY but limit to top 2 per group

    I think this explains things...

    Number of items sold in a transaction

    Department / Customer / SalesPersonID / Sales

    A / Mr Smith / 1 / 3
    A / Mr Brown / 2 / 2
    A / Mr Roberts / 1 / 5
    A / Mrs Smith / 2 / 1
    A / Mr Andrews / 2 / 1
    A / Mrs Black / 3 / 2
    B / Mr Smith / 1 / 1
    etc.
    (there will be many departments)

    SELECT Department, SalesPersonID, totSales FROM $table GROUP BY Department, SalesPersonID ORDER BY SUM(Sales) AS totSales

    This will group and return them all, but I only want the TOP 2 per department.

    Thanks in advance.

  2. #2
    SitePoint Enthusiast erangalp's Avatar
    Join Date
    Oct 2010
    Location
    Israel
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Should have explained it in full in your original post

    This article covers what you are looking for - http://www.xaprb.com/blog/2006/12/07...-group-in-sql/

    You should read it all, but your answer lies under "Select the top N rows from each group"


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
  •