Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Question Unanswered: How to limit the result?

    I have a table as shown below:

    CREATE TABLE `sales` (
    `ID` int(11) PRIMARY KEY,
    `SALES_PERSON` int(11) default NULL,
    `PRODUCT` int(11) default NULL,
    `MONTH` int(11) default NULL,
    `AMOUNT` int(11) default NULL,
    KEY `SALES_IDX1` (`SALES_PERSON`),
    KEY `SALES_IDX2` (`MONTH`),
    KEY `SALES_IDX3` (`PRODUCT`)
    ) ENGINE=MyISAM

    I like to know top 3 products sold by each sales person on every month. What I am doing now is that select the DISTINCT SALES_PERSON in the table for a particular month and then iterate over each sales person in my java application level and applying one more query to get the final result. The queries are as follows:

    First Query:
    SELECT DISTINCT SALES_PERSON FROM SALES WHERE MONTH=5;

    Second Query:
    SELECT SUM(AMOUNT) AS SUMM, SALES_PERSON, MONTH, PRODUCT FROM SALES WHERE SALES_PERSON=102 GROUP BY SALES_PERSON, MONTH, PRODUCT ORDER BY SUMM DESC LIMIT 3;

    Is it possible to achieve the same logic in a single query? I assume that a single query will give better performance.
    Please note that we already optimized the query performance by adding indexes.
    Please use the attached sample data if you need.

    MySQL: MySQL 5.0.36-enterprise-nt
    OS: Windows 2003 SP2
    Application Language: Java
    Connectivity : JDBC


    Regards,
    Jake


    SAMPLE DATA:
    1 101 1001 5 1000
    2 101 1002 5 1000
    4 101 1001 5 1000
    3 101 1002 5 1000
    5 102 1001 5 50
    6 102 1002 5 50
    7 102 1001 5 50
    8 102 1002 5 50
    9 101 1001 5 1000
    10 101 1002 5 1000
    11 101 1001 5 1000
    12 101 1002 5 1000
    13 102 1001 5 50
    14 102 1002 5 50
    15 102 1001 5 50
    16 102 1002 5 50
    17 101 1001 5 1000
    18 101 1002 5 1000
    19 102 1001 5 50
    20 102 1002 5 50
    21 101 1003 5 1000
    22 101 1003 5 1000
    24 101 1003 5 1000
    23 101 1003 5 1000
    29 101 1003 5 1000
    30 101 1003 5 1000
    31 101 1003 5 1000
    32 101 1003 5 1000
    37 101 1003 5 1000
    38 101 1003 5 1000
    25 102 1003 5 50
    26 102 1003 5 50
    27 102 1003 5 50
    28 102 1003 5 50
    33 102 1003 5 50
    34 102 1003 5 50
    35 102 1003 5 50
    36 102 1003 5 50
    39 102 1003 5 50
    40 102 1003 5 50
    41 101 1004 5 1000
    42 101 1004 5 1000
    44 101 1004 5 1000
    43 101 1004 5 1000
    49 101 1004 5 1000
    50 101 1004 5 1000
    51 101 1004 5 1000
    52 101 1004 5 1000
    57 101 1004 5 1000
    58 101 1004 5 1000
    45 102 1004 5 50
    46 102 1004 5 50
    47 102 1004 5 50
    48 102 1004 5 50
    53 102 1004 5 50
    54 102 1004 5 50
    55 102 1004 5 50
    56 102 1004 5 50
    59 102 1004 5 50
    60 102 1004 5 50

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    select month
         , sales_person
         , product 
         , summ
      from (
           select month
                , sales_person
                , product 
                , sum(amount) as summ
             from sales 
           group 
               by month
                , sales_person
                , product 
           ) as t
     where ( select count(*)
               from (
                    select month
                         , sales_person
                         , product 
                         , sum(amount) as summ
                      from sales 
                    group 
                        by month
                         , sales_person
                         , product 
                    )
              where month = t.month
                and summ > t.summ ) < 3
    this can be simplified if you define a view for the GROUP BY subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Who wants to try a magical hacked MySQL specific analytic function?

    Code:
    SELECT sales_person,product,total,`month` FROM
      (SELECT if(@person = sales_person
              ,@rank := @rank + 1
              ,@rank := 1 + least(0,@person := sales_person)
             ) rank,total,sales_person,product,`month`
      FROM (
          SELECT sales_person,product,`month`,SUM(amount) as total
          FROM sales
          GROUP BY sales_person,product,`month`
          ORDER BY total DESC
      ) x, (SELECT @person := 0, @rank := 0) y
    ) z
    WHERE rank < 4;
    p.s. edited to include product ids...
    p.p.s edited to include months
    Last edited by aschk; 05-29-07 at 10:56.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Aside from the obvious nastiness of my function : try Rudy's

  5. #5
    Join Date
    Feb 2004
    Posts
    107
    Sorry for the late reply... it worked beautifully with a performance boost of 33%... Thanks for your help.

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Which query had the performance boost? I'm curious to know which one you went with in the end.

  7. #7
    Join Date
    Feb 2004
    Posts
    107
    I went with your query...

Posting Permissions

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