How to retrieve data by following conditions?

Question

+--------+------+---------------------+
| userId | Cmd  | Time                |
+--------+------+---------------------+
|      1 |  A   | 2013-06-21 13:15:08 |
|      1 |  B   | 2013-07-31 15:58:53 |
|      3 |  A   | 2013-07-31 16:01:42 |
|      4 |  A   | 2013-05-01 11:05:08 |
|      4 |  B   | 2013-07-31 16:06:32 |
|      6 |  B   | 2013-07-31 17:16:08 |
|      7 |  A   | 2013-04-06 17:20:36 |
|      3 |  B   | 2013-04-15 06:26:14 |
|      7 |  B   | 2013-07-31 14:53:49 |
|      6 |  A   | 2013-07-31 09:56:27 |
+--------+------+---------------------+

How to get userIDs where their last Cmd is B?

Expected result:

+--------+------+---------------------+
| userId | Cmd  | Time                |
+--------+------+---------------------+
|      1 |  B   | 2013-07-31 15:58:53 |
|      4 |  B   | 2013-07-31 16:06:32 |
|      6 |  B   | 2013-07-31 17:16:08 |
|      7 |  B   | 2013-07-31 14:53:49 |
+--------+------+---------------------+

What do you mean by last cmd? All users have exactly one cmd in your sample data. — Noel, Aug 14 '13 at 06:01
@ILMIR - If you want where last Cmd is B then why userID=3 is not there in your expected output? — Himanshu, Aug 14 '13 at 06:16
@hims056 - because that user(userID=3) has latest Cmd A 2013-07-31 16:01:42 whereas B Cmd has time 2013-04-15 06:26:14 — abdulmanov.ilmir, Aug 14 '13 at 06:23

score 2 · Accepted Answer · answered Aug 14 '13 at 06:32

2

Here you need to self join using MAX(Time), UserId and Cmd='B' like this:

SELECT t1.* FROM Table1 t1
JOIN
(
   SELECT UserId, MAX(Time) MaxTime FROM Table1
   GROUP BY UserID
)t2
ON t1.UserID = t2.UserId
AND t1.Time = t2.MaxTime
AND t1.Cmd = 'B';

Output:

| USERID | CMD |                        TIME |
----------------------------------------------
|      1 |   B | July, 31 2013 15:58:53+0000 |
|      4 |   B | July, 31 2013 16:06:32+0000 |
|      6 |   B | July, 31 2013 17:16:08+0000 |
|      7 |   B | July, 31 2013 14:53:49+0000 |

See this SQLFiddle

answered Aug 14 '13 at 06:32

Himanshu

31,810
31
111
133

1

Yeah your query works very well. Thanks a lot:) sqlfiddle.com is usefull site, thanks for the link. – abdulmanov.ilmir Aug 14 '13 at 07:16
@ILMIR - Glad to help you. And yeah that is very helpful tool. – Himanshu Aug 14 '13 at 07:19
Allow me ask you another question. let's assume that there are more than 2 Cmds (suppose 5) Here we are using current conditions except one - we will not seek B Cmd, but will looking for all Cmds where NOT A. I tested it with t1.Cmd != 'A', but result contains duplicated userIds. How to do it correctly? thanks in advance. – abdulmanov.ilmir Aug 14 '13 at 08:10
@ILMIR Leaving out the `AND t1.Cmd = 'B'` should give you the most recent record per userId. There should only be duplicate userId's if the Time column contains duplicates (UserId/MaxTime combination matches multiple records). Therefore, changing `AND t1.Cmd = 'B'` to `AND t1.Cmd <> 'A'` should give the desired result. – Sam Aug 14 '13 at 08:40
1

@ILMIR - It should work properly even if you use `AND t1.cmd !='A'` because we are using `MAX()` function which will return you the record with max `Time`. See [this updated SQLFiddle](http://sqlfiddle.com/#!2/31804/2) with more than two Cmds. But yes if there are two record with same maximum time then it will return duplicate result userids. But you can filter those using `DISTINCT` keyword [like this](http://sqlfiddle.com/#!2/063aa/2). But if CMD is different then it will not filter duplicate userID because both records are not same. [like this](http://sqlfiddle.com/#!2/f086f/1). – Himanshu Aug 14 '13 at 09:00

Sam · Answer 2 · 2013-08-14T06:42:39.300

First get the last date for each Cmd, then select the records matching that date:

SELECT userId
FROM SomeTable
JOIN ( SELECT Cmd,
              MAX(Time) AS MaxTime
       FROM SomeTable
       GROUP BY Cmd
     ) MaxTable ON SomeTable.Cmd = MaxTable.Cmd
               AND SomeTable.Time = MaxTable.MaxTime

If you only want B-records:

SELECT userId
FROM SomeTable
JOIN ( SELECT Cmd,
              MAX(Time) AS MaxTime
       FROM SomeTable
       GROUP BY Cmd
       WHERE Cmd = 'B'
     ) MaxTable ON SomeTable.Cmd = MaxTable.Cmd
               AND SomeTable.Time = MaxTable.MaxTime

EDIT: Misunderstood the question. See hims056's answer. He is doing the same thing as I do, but grouping on UserId instead of Cmd. Compare:

SELECT SomeTable.*
FROM SomeTable
JOIN ( SELECT userId,
              MAX(Time) AS MaxTime
       FROM SomeTable
       GROUP BY userId

     ) MaxTable ON SomeTable.userId= MaxTable.userId
               AND SomeTable.Time = MaxTable.MaxTime
WHERE SomeTable.Cmd = 'B'

score 0 · Answer 3 · answered Aug 27 '13 at 09:54

0

The query is:

SELECT userID
FROM TABLE_COMMANDE
WHERE 
(
 TIME = (SELECT MAX(TIME) FROM TABLE_COMMANDE WHERE CMD LIKE 'B')
)

answered Aug 27 '13 at 09:54

Rostand Richard

31
3

score -1 · Answer 4 · edited Aug 14 '13 at 06:35

-1

select userID
from your_table
where CMD like 'B'

edited Aug 14 '13 at 06:35

Himanshu

31,810
31
111
133

answered Aug 14 '13 at 06:00

DNac

2,663
8
31
54

4

When you don't use `%` sign with `LIKE` then its same as using `=`. Although i'm not the downvoter – Hanky Panky Aug 14 '13 at 06:03
For sure. However, IMHO this depends on personal preferences of people. The important thing is to get the correct result, not how the SQL is written (not taking the performance into account). – DNac Aug 14 '13 at 06:08
1

I did not declare your answer as invalid, nor do I have any right to that. I just mentioned the comment for any reader's help. But since you put it up, I would like to mention that `=` is faster than using `LIKE` for exact string match, review here http://stackoverflow.com/questions/559506/mysql-using-like-vs-for-exact-string-match – Hanky Panky Aug 14 '13 at 06:10

How to retrieve data by following conditions?

4 Answers4

See this SQLFiddle