Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Dec 2005
    Posts
    217
    Thanks
    1
    Thanked 0 Times in 0 Posts

    How to query for this?

    Hi

    I have following two tables:
    tbl_customers

    Code:
        id  customer_name  customer_email    
    ------  -------------  ------------------
         1  Name1          email@hotmail.com 
         2  Name2          email2@hotmail.com
         3  Name3          email3@hotmail.com
    And tbl_customer_category_clicks

    Code:
        id  category_id  customer_id  clicks      
    ------  -----------  -----------  ------
         3           22            2       4
         4           23            2       22
         5           19            2       1         
         6           12            2       6
         8            5            1      10         
         9           20            1      20

    What i want?

    I want to list all the customer from tbl_customer with category_id having maximum no of clicks.
    For example, output should be:

    Code:
    category_id  customer_id  clicks      
    -----------  -----------  ------     
               20           1      20  
               23           2      22  
               0            3      0
    Thanks

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    try:
    Code:
    select category_id, customer_id, max(clicks) as clicks
    from tbl_customer_category_clicks
    group by customer_id
    best regards

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,103
    Thanks
    80
    Thanked 4,554 Times in 4,518 Posts
    No, Oesxyl. Doesn't work.

    Slightly simplified table and then your query:
    Code:
    mysql> select * from clicks;
    +--------+-------+--------+
    | custid | catid | clicks |
    +--------+-------+--------+
    |      2 |    22 |      4 |
    |      2 |    23 |     22 |
    |      2 |    19 |      1 |
    |      2 |    12 |      6 |
    |      1 |     5 |     10 |
    |      1 |    20 |     20 |
    +--------+-------+--------+
    6 rows in set (0.00 sec)
    
    mysql> select catid, custid, max(clicks) as m from clicks group by custid;
    +-------+--------+------+
    | catid | custid | m    |
    +-------+--------+------+
    |     5 |      1 |   20 |
    |    22 |      2 |   22 |
    +-------+--------+------+
    2 rows in set (0.02 sec)
    Yes, it gets the right max(clicks) value, but look at the catid. Can you say "oops"?

    This is actually a much more difficult problem than you might think.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Old Pedant View Post
    No, Oesxyl. Doesn't work.

    Slightly simplified table and then your query:
    Code:
    mysql> select * from clicks;
    +--------+-------+--------+
    | custid | catid | clicks |
    +--------+-------+--------+
    |      2 |    22 |      4 |
    |      2 |    23 |     22 |
    |      2 |    19 |      1 |
    |      2 |    12 |      6 |
    |      1 |     5 |     10 |
    |      1 |    20 |     20 |
    +--------+-------+--------+
    6 rows in set (0.00 sec)
    
    mysql> select catid, custid, max(clicks) as m from clicks group by custid;
    +-------+--------+------+
    | catid | custid | m    |
    +-------+--------+------+
    |     5 |      1 |   20 |
    |    22 |      2 |   22 |
    +-------+--------+------+
    2 rows in set (0.02 sec)
    Yes, it gets the right max(clicks) value, but look at the catid. Can you say "oops"?

    This is actually a much more difficult problem than you might think.
    i didn't test it, i think you are right. Maybe catid can be replaced with a select with a where clause based on the custid and m. Not sure how and if will work because of the order how sql return partial results.

    best regards

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,103
    Thanks
    80
    Thanked 4,554 Times in 4,518 Posts
    Look here:
    Code:
    mysql> select * from cust;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | adam |
    |    2 | bob  |
    |    3 | carl |
    +------+------+
    3 rows in set (0.00 sec)
    
    mysql> select * from clicks;
    +--------+-------+--------+
    | custid | catid | clicks |
    +--------+-------+--------+
    |      2 |    22 |      4 |
    |      2 |    23 |     22 |
    |      2 |    19 |      1 |
    |      2 |    12 |      6 |
    |      1 |     5 |     10 |
    |      1 |    20 |     20 |
    +--------+-------+--------+
    6 rows in set (0.00 sec)
    
    mysql> select cust.id, cust.name, clicks.catid, x.maxclicks
        -> from clicks inner join (
        ->     select custid, max(clicks) as maxclicks from clicks
        ->     group by custid ) AS x
        -> on ( clicks.custid = X.custid AND clicks.clicks = x.maxclicks )
        -> right join cust
        -> on cust.id = X.custid
        -> order by cust.id;
    +------+------+-------+-----------+
    | id   | name | catid | maxclicks |
    +------+------+-------+-----------+
    |    1 | adam |    20 |        20 |
    |    2 | bob  |    23 |        22 |
    |    3 | carl |  NULL |      NULL |
    +------+------+-------+-----------+
    3 rows in set (0.03 sec)
    BUT...

    But if you have a case where the MAX(clicks) value is NOT UNIQUE for a given user, then you will get multiple results for that user.

    For example, let me add another row to that clicks table:
    Code:
    mysql> insert into clicks values(1,999,20);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from clicks;
    +--------+-------+--------+
    | custid | catid | clicks |
    +--------+-------+--------+
    |      2 |    22 |      4 |
    |      2 |    23 |     22 |
    |      2 |    19 |      1 |
    |      2 |    12 |      6 |
    |      1 |     5 |     10 |
    |      1 |    20 |     20 |
    |      1 |   999 |     20 |
    +--------+-------+--------+
    7 rows in set (0.00 sec)
    
    mysql> select cust.id, cust.name, clicks.catid, x.maxclicks
        -> from clicks inner join (
        ->     select custid, max(clicks) as maxclicks from clicks
        ->     group by custid ) AS x
        -> on ( clicks.custid = X.custid AND clicks.clicks = x.maxclicks )
        -> right join cust
        -> on cust.id = X.custid
        -> order by cust.id;
    +------+------+-------+-----------+
    | id   | name | catid | maxclicks |
    +------+------+-------+-----------+
    |    1 | adam |    20 |        20 |
    |    1 | adam |   999 |        20 |
    |    2 | bob  |    23 |        22 |
    |    3 | carl |  NULL |      NULL |
    +------+------+-------+-----------+
    4 rows in set (0.00 sec)
    See it? SQL can't guess which of the two records (id 1, cat id either 20 or 999) you want it to choose, so it will give you both.

    That may be what you need, in a real world situation. If you want to arbitrarily pick one, that's not hard.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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