...

View Full Version : How to query for this?



PHPycho
04-19-2011, 09:35 AM
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

oesxyl
04-19-2011, 02:11 PM
try:


select category_id, customer_id, max(clicks) as clicks
from tbl_customer_category_clicks
group by customer_id


best regards

Old Pedant
04-19-2011, 09:09 PM
No, Oesxyl. Doesn't work.

Slightly simplified table and then your query:



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.

oesxyl
04-19-2011, 09:19 PM
No, Oesxyl. Doesn't work.

Slightly simplified table and then your query:



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

Old Pedant
04-19-2011, 09:23 PM
Look here:

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:


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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum