Select first value in column and exclude the rest. DISTINCT for colums?

Question

Im trying to sort out my latest values from a number on units sending data to my MYSQL database on a frequent level.

I get several datapoint and would like to display some of them. Sorted by date so i get the newest only for each unit. I use mu IMEI numbers of each unit as id and would like to sort from them.

My problem is that when i have sorted the whole dataset in the order i would like i cant exclude the rest of the data. When using DISTINCT i unly get uniq rows. And since I have a timestamp more or less all of them are unique.

This is the query I'm currently using.

"SELECT DISTINCT(imei), imsi, battery, charging, time, median, latitude, longitude FROM {$table} GROUP BY time desc"

How can I sort this so my newest imei only shows up. Excluding the rest?

I've been trying DISTINCT, TOP, some exemple code using HAVING and different kinds of WHERE without having any success.

Hints and tips for where I can get more knowledge about this question and how to solve it?

This os the top results i currently get form my Query.

imei    imsi    battery charging    time    median  latitude    longitude
355675061138699 9240075810046549    33  1   2015-12-21 14:17:41 301 61 29.8763  16 20.9981
355675061138699 9240075810046549    33  1   2015-12-21 14:16:26 300 61 29.8763  16 20.9981
355675061138699 9240075810046549    33  1   2015-12-21 14:15:10 301 61 29.8763  16 20.9981
355675061138699 9240075810046549    33  1   2015-12-21 14:13:53 301 61 29.8763  16 20.9981
355675061138699 9240075810046549    33  1   2015-12-21 14:12:38 299 61 29.8763  16 20.9981
355675061138699 9240075810046549    0   1   2015-12-21 14:11:22 301 61 29.8763  16 20.9981

There seem to be a differing number of column names and columns ?!? — Strawberry, Dec 22 '15 at 15:58
DISTINCT is not a function, and see the manual: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html — Strawberry, Dec 22 '15 at 16:47

score 1 · Answer 1 · answered Dec 22 '15 at 15:38

1

If you want the most recently inserted entry in a table, you could always SELECT * FROM table WHERE id = (SELECT MAX(id) from table)

answered Dec 22 '15 at 15:38

Redbeard011010

954
6
20

score 1 · Accepted Answer · answered Dec 22 '15 at 15:40

1

One method is to use a correlated subquery:

SELECT imei, imsi, battery, charging, time, median, latitude, longitude
FROM {$table} t
WHERE time = (SELECT MAX(t2.time)
              FROM {$table} t2
              WHERE t2.imei = t.imei
             )
ORDER BY time desc;

For optimal performance, you want an index on (imei, time). and (time).

answered Dec 22 '15 at 15:40

Gordon Linoff

1,242,037
58
646
786

Thanks a lot. Worked just like I wanted. Any suggestion on where I can find subquerys well explained to understand better myself? – Johannes Löfgren Dec 23 '15 at 19:34

score 0 · Answer 3 · answered Dec 22 '15 at 17:32

If you are using group by time, then distinct(imei) is useless. it will give you same result without using distinct(imei). Here in query you are using group by time, in result query give you data group by time with duplicate imei. if you use group by time, imei then it will give you result with distinct time, imei (If you see both field : either time or imei will be distinct with other row).

Select first value in column and exclude the rest. DISTINCT for colums?

3 Answers3