CODEWITHSUNDEEP
sqlmysql
nickifrandsen
nickifrandsen

Reputation: 433

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

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.

Upvotes: 0

Views: 814

Answers (5)

nickifrandsen
nickifrandsen

Reputation: 433

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 :)

Upvotes: -1

ovais.tariq
ovais.tariq

Reputation: 2625

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

Upvotes: 1

shamittomar
shamittomar

Reputation: 46702

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

Upvotes: 0

Maulik Vora
Maulik Vora

Reputation: 2584

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

Upvotes: 0

Nicolas78
Nicolas78

Reputation: 5144

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)

Upvotes: 1

Related Questions