CODEWITHSUNDEEP
mysqlsqlselectgroup-by
Raptor
Raptor

Reputation: 54212

SQL : a GROUP BY issue in MySQL

Here is the database table

╔════╦═════════════╦══════════════════╦═══════╗
║ id ║ customer_id ║    last_seen     ║ param ║
╠════╬═════════════╬══════════════════╬═══════╣
║  1 ║       12345 ║ 2012-08-01 12:00 ║     1 ║
║  2 ║       22345 ║ 2012-08-01 12:00 ║     1 ║
║  3 ║       32345 ║ 2012-08-01 12:00 ║     1 ║
║  4 ║       42345 ║ 2012-08-01 12:00 ║     1 ║
║  5 ║       52345 ║ 2012-08-01 12:00 ║     1 ║
║  6 ║       12345 ║ 2012-09-01 12:00 ║     2 ║
║  7 ║       12345 ║ 2012-10-01 12:00 ║     3 ║
╚════╩═════════════╩══════════════════╩═══════╝

where id is an AUTO INCREMENT primary key.

What I want to achieve is to get the last record of each customer_id. Expected Result :

╔════╦═════════════╦══════════════════╦═══════╗
║ id ║ customer_id ║    last_seen     ║ param ║
╠════╬═════════════╬══════════════════╬═══════╣
║  2 ║       22345 ║ 2012-08-01 12:00 ║     1 ║
║  3 ║       32345 ║ 2012-08-01 12:00 ║     1 ║
║  4 ║       42345 ║ 2012-08-01 12:00 ║     1 ║
║  5 ║       52345 ║ 2012-08-01 12:00 ║     1 ║
║  7 ║       12345 ║ 2012-10-01 12:00 ║     3 ║
╚════╩═════════════╩══════════════════╩═══════╝

I tried this SQL, but it returns incorrect result :

SELECT customer_id, param, last_seen 
FROM `my_table` 
GROUP BY customer_id 
ORDER BY last_seen DESC

What am I missing here ?

UPDATE: Table structure ( output of DESC my_table )

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| customer_id  | varchar(8)   | NO   |     | NULL    |                |
| last_seen    | datetime     | NO   |     | NULL    |                |
| param        | int(11)      | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Upvotes: 3

Views: 153

Answers (2)

valex
valex

Reputation: 24134

Here is a SQLFiddle demo

In MySQL you can do it in this way:

select ID,CUSTOMER_ID,LAST_SEEN,PARAM from
(
   select t.*,
       if(@i=customer_id,0,1) isLast,
       @i:=customer_id 
       from `my_table` t,(select @i:=0) t1 
   order by customer_id,last_seen desc
) t2 where isLAst=1

Upvotes: 0

John Woo
John Woo

Reputation: 263683

The idea behind the subquery is that it separately gets the latest last_seen value for each Customer_ID. The result of the subquery is then joined with the original table provided that the condition must be met: the CustomerID and the dates must match with each other.

SELECT  a.*
FROM    Customer a
        INNER JOIN
        (
            SELECT Customer_ID, MAX(last_seen) maxDate
            FROM Customer
            GROUP BY Customer_ID
        ) b ON a.Customer_ID = b.Customer_ID AND
                a.last_seen = b.maxDate
ORDER BY a.ID

Follow-up Question: can you please check if the the last_seen value of record id 6 is correct?

Upvotes: 7

Related Questions