How to query for this?

Databases
1

Hi

I have following two tables:
tbl_customers

    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

    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:

category_id  customer_id  clicks      
-----------  -----------  ------     
           20           1      20  
           23           2      22  
           0            3      0

Thanks

2

And if a customer has more categories with the same max number of clicks?

3

Then anyone would do. Not that there should be only one result for category_id per customer.

Thanks

4

Try this (didn’t test it)


SELECT
    c.id
  , cc2.category_id
  , cc2.clicks
FROM tbl_customers AS c
LEFT OUTER JOIN 
  (SELECT  
       cc.id
     , cc.category_id
     , cc.clicks
   FROM tbl_customer_category_clicks AS cc
   INNER JOIN
     (SELECT 
          customer_id
        , MAX(clicks) AS maxclicks
      FROM tbl_customer_category_clicks
      GROUP BY customer_id
     ) AS mc
   ON cc.customer_id = mc.customer_id
   AND cc.clicks = mc.maxclicks
   GROUP BY cc.id
  ) AS cc2
ON c.id = cc2.customer_id
5

guido, your inner GROUP BY is truly evil

nice one!!

:smiley: :smiley:

6

Thanks Guido & Rudy.
That worked like a charm.