hi folks - I was hoping someone (Rudy?) could help me understand what I’m missing here; as I mentioned to Rudy earlier I know enough about SQL and databases in general to be dangerous.
I have two tables: customers & visits; one customer can have multiple visits; what I’m trying to come up with is a query that will provide me with the latest visit per customer:
mysql> select id, name from customers;
+-----+-----------------+
| id | name |
+-----+-----------------+
| 101 | AAA Electronics |
| 102 | BBB Electronics |
| 103 | CCC Electronics |
| 104 | DDD Electronics |
+-----+-----------------+
4 rows in set (0.00 sec)
mysql> select id, date, customer_id from visits order by customer_id;
+----+---------------------+-------------+
| id | date | customer_id |
+----+---------------------+-------------+
| 1 | 2012-02-20 00:00:00 | 101 | <----
| 2 | 2012-02-01 00:00:00 | 101 |
| 3 | 2012-02-03 00:00:00 | 101 |
| 4 | 2012-02-05 00:00:00 | 101 |
| 5 | 2012-01-07 00:00:00 | 102 |
| 6 | 2012-01-08 00:00:00 | 102 |
| 7 | 2012-01-09 00:00:00 | 102 |
| 8 | 2012-01-25 00:00:00 | 102 | <----
| 9 | 2011-12-01 00:00:00 | 103 |
| 10 | 2011-12-02 00:00:00 | 103 |
| 11 | 2011-12-25 00:00:00 | 103 | <----
| 12 | 2011-12-03 00:00:00 | 103 |
| 13 | 2011-12-04 00:00:00 | 103 |
+----+---------------------+-------------+
13 rows in set (0.00 sec)
mysql> SELECT c.name, c.id customer_id, v.id visit_id, max(v.date) visit_date
-> FROM customers c join VISITS v ON v.customer_id = c.id
-> GROUP BY c.name;
+-----------------+-------------+----------+---------------------+
| name | customer_id | visit_id | visit_date |
+-----------------+-------------+----------+---------------------+
| AAA Electronics | 101 | 1 | 2012-02-20 00:00:00 |
| BBB Electronics | 102 | 5 | 2012-01-25 00:00:00 |
| CCC Electronics | 103 | 9 | 2011-12-25 00:00:00 |
+-----------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)
I seem to be almost there, except that the visit_id isn’t getting grouped correctly.
what I need for the query to produce is this:
+-----------------+-------------+----------+---------------------+
| name | customer_id | visit_id | visit_date |
+-----------------+-------------+----------+---------------------+
| AAA Electronics | 101 | 1 | 2012-02-20 00:00:00 |
| BBB Electronics | 102 | 8 | 2012-01-25 00:00:00 |
| CCC Electronics | 103 | 11 | 2011-12-25 00:00:00 |
+-----------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)
I’m starting to think I might need two queries with a UNION in there somewhere… thanks.
-Len