MySQL problem! Needs to select unique value from one row only

Question

Hi Everyone:)

I'm creating this app where it's important to register if a person is active or not on the current day. My table structure looks like this:

| Field     | Type        | Null | Key | Default           | Extra          |
+-----------+-------------+------+-----+-------------------+----------------+
| id        | int(11)     | NO   | PRI | NULL              | auto_increment | 
| kid_id    | int(11)     | NO   |     | NULL              |                | 
| status    | varchar(50) | NO   |     | NULL              |                | 
| timestamp | timestamp   | NO   |     | CURRENT_TIMESTAMP |                | 

What's important for me; is to select only values with timestamp equal to the current day. Next I need to check if the latest status is "active" or "inactive" of course based on kid_id. What I've figured out so far is this.

SELECT kid_id , status , timestamp FROM actions WHERE date(timestamp) = CURDATE() ORDER BY timestamp DESC;

Which returns these values:

+--------+----------+---------------------+
| kid_id | status   | timestamp           |
+--------+----------+---------------------+
|      4 | active   | 2010-08-23 12:10:03 | 
|      3 | inactive | 2010-08-23 10:53:18 | 
|      3 | active   | 2010-08-23 10:53:10 | 
+--------+----------+---------------------+

Only problem now is that i receive both the active and inactive status with "kid_id" = "3". So my question is how do i only select the latest status from each kid_id.

In advance thank you very much for your help. It's appreciated.

---------- EDIT

I'll try to make my point a little more clear.

This is my table as it looks right now...

+--------+----------+---------------------+
| kid_id | status   | timestamp           |
+--------+----------+---------------------+
|      3 | inactive | 2010-08-23 18:32:19 | 
|      4 | active   | 2010-08-23 12:10:03 | 
|      3 | active   | 2010-08-23 10:53:10 | 
+--------+----------+---------------------+
3 rows in set (0.00 sec)

I want to retrieve these values and only these

|      3 | inactive | 2010-08-23 18:32:19 | 
|      4 | active   | 2010-08-23 12:10:03 |

All of the solutions suggested below returns these values:

|      4 | active   | 2010-08-23 12:10:03 | 
|      3 | active   | 2010-08-23 10:53:10 |

By the way... Thanks for all the responses so soon I'm really grateful for all the help.

Answer 1

first, your WHERE-clause should read

WHERE ... AND status = 'active'

If you can have different rows for a single user on a given day that are set to active, you can say

SELECT DISTINCT kid, status ...

Note that this returns unique values for everything you select, so you shouldn't select the timestamp because different timestamps at the same day, for the same user, would again show up as several rows (as they're non-distinct)

Answer 2

use group by kid_id which only selects latest row as you have ordered by timestamp

SELECT kid_id , status , timestamp FROM actions WHERE date(timestamp) = CURDATE() GROUP BY kid_id ORDER BY timestamp DESC

Answer 3

Use group by like this:

 SELECT kid_id , status , timestamp, count(*) as TotalMatches FROM actions WHERE date(timestamp) = CURDATE() GROUP BY kid_id ORDER BY timestamp DESC

[EDIT]: For selecting only active statuses:

 SELECT kid_id , status , timestamp, count(*) as TotalMatches FROM actions WHERE date(timestamp) = CURDATE() AND status = 'active' GROUP BY kid_id ORDER BY timestamp DESC

Answer 4

this should do

SELECT a1.*
FROM actions AS a1 
   INNER JOIN (SELECT kid_id, MAX(`timestamp`) AS `timestamp` 
                 FROM actions 
                 WHERE date(timestamp) = CURDATE() 
                 GROUP BY kid_id) AS a2 
    ON a2.kid_id = a1.kid_id AND a2.timestamp = a1.timestamp

Answer 5

Hi Folks... I've been out the office today. In the mean time I've figured out a way to solve my problem. I used a subselect which worked.

In the end my code looks a little like ovais.tariq's

Here is my solution:

SELECT * 
            FROM (
                SELECT * FROM actions 
                AS a
                WHERE date(timestamp) = curdate() 
                AND timestamp = (
                    SELECT max(timestamp) 
                    FROM actions 
                    AS b
                    WHERE a.kid_id = b.kid_id
                    )
                )
            AS c

Thanks everyone for your help :)