Select first row of data block

Question

I have a table like this:

**id - userId - session - time**<br>
1 ---- 1 -------- 1 ----- 20:12:10<br>
2 ---- 1 -------- 1 ----- 20:45:11<br>
3 ---- 1 -------- 1 ----- 20:55:12<br>
4 ---- 2 -------- 1 ----- 10:45:11<br>
5 ---- 2 -------- 1 ----- 10:55:11<br>
6 ---- 1 -------- 2 ----- 05:58:16<br>
7 ---- 1 -------- 2 ----- 05:45:11<br>
8 ---- 1 -------- 2 ----- 05:45:11<br>
...

How do I select only the first row of user with id 1 (only column session and time) in mySQL, like this:

**session - time**<br>
1 -----     20:12:10<br>
2 -----     05:58:16<br><br>

possible duplicate of [How to select the first row for each group in MySQL?](http://stackoverflow.com/questions/2739474/how-to-select-the-first-row-for-each-group-in-mysql) — Air, Apr 09 '15 at 17:25
do you mean the row with earliest timestamp? or with the lowest id? — 1010, Apr 09 '15 at 18:48
Sad to say - this is a thing that MySQL simply doesn't have. read this: http://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns or look for "mysql select disctinct on one column' - you'll find a LOT of topics about the same thing... You really shouldn't rely on `GROUP BY`, as you NEVER know which row will be returned. adding `ORDER BY` afterwards DOES NOT work. If possible - consider switching to Postgres. There - you can do `SELECT DISTINCT ON (a,b) c,d from XX` like here: http://sqlfiddle.com/#!15/2d98b/4. — murison, Apr 09 '15 at 19:35

1010 · Answer 1 · 2015-04-09T19:08:07.287

1

if the order is given by the id field, you should get the session and time of the row with the lowest id:

SELECT session, time
  FROM your_table
 WHERE id IN (SELECT MIN(id) FROM your_table WHERE userId = 1 GROUP BY session)
 ORDER BY session

Edited because I had missed that you wanted the first row of every session.

edited Apr 09 '15 at 19:08

answered Apr 09 '15 at 19:01

1010

1,779
17
27

score -1 · Answer 2 · answered Apr 09 '15 at 16:42

-1

SELECT session, MIN(time) as time
FROM my_table
WHERE userId=1
GROUP BY session

answered Apr 09 '15 at 16:42

Alex

16,739
1
28
51

Thanks! But what if the first rows time is 23:55 and the other times on the same session are less like: 01:05.. and I only want the first row with time (23:55). – user3800924 Apr 09 '15 at 16:52
@user3800924 to get **first row** it is not clear and not good mysql practice. even the solution you choose from Kkinsey will bring you wrong result once in a while. because there is no rule for mysql server that it MUST return first value when do `group by`. the question is what is your real expected result and why. – Alex Apr 09 '15 at 18:36

score -1 · Answer 3 · answered Apr 09 '15 at 16:43

-1

I think "group by" is your friend:

mysql> select distinct (userid),session,time from testing;
+--------+---------+------+
| userid | session | time |
+--------+---------+------+
|      1 |       1 | 123  |
|      1 |       1 | 124  |
|      2 |       1 | 124  |
|      2 |       1 | 125  |
|      2 |       3 | 125  |
|      2 |       4 | 125  |
|      3 |       1 | 127  |
+--------+---------+------+
7 rows in set (0.00 sec)

mysql> select userid,session,time from testing group by userid;
+--------+---------+------+
| userid | session | time |
+--------+---------+------+
|      1 |       1 | 123  |
|      2 |       1 | 124  |
|      3 |       1 | 127  |
+--------+---------+------+

Hope this helps.

answered Apr 09 '15 at 16:43

Kevin_Kinsey

2,285
1
22
23

O yes it was that easy! So odd I tested also GROUP BY first but it did not work but now it does... strange – user3800924 Apr 09 '15 at 17:05
1

in plain sql it is wrong to select fields that are not part of the group by list. you can in mysql but I don't know how it chooses the value to return. – 1010 Apr 09 '15 at 18:55
1

See [this](http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html) "the select list can refer to nonaggregated columns not named in the GROUP BY clause ... The server is free to choose **any value** from each group, so unless they are the same, the values chosen are indeterminate." – 1010 Apr 09 '15 at 19:35
as mentioned above - you never know from which row will the values be returned. This may work sometimes, but should be never relied on. – murison Apr 09 '15 at 19:40

Select first row of data block

3 Answers3