How to select multiple columns with a distinct value from one column

Question

So I've been trying to find an answer on here that will work for my situation, but I am having a bit of a problem finding one. Basically, I have a table comprising 10,000 rows or more and there are some things that repeat in the table. For example, I have 3 units and they each have a unit_id that is repeating. These units also have a long/lat associated with them. How would I go about selecting the most recent long/lat of each unique device?

entry_id | unit_id | lat | long
1         123       45     62
2         123       15     24 
3         123       12     11
4         456       55     31
5         456       01     12
6         123       15     46

The higher the entry_id, the newer the entry. So in this example I would want to select entries 5 and 6 so that I have the most recent lat/long for these devices. Any ideas?

Answer 1

Assuming that "most recent" means the highest entry_id, here is one method:

select t.*
from t
where t.entry_id = (select max(t2.entry_id)
                    from t t2
                    where t2.unit_id = t.unit_id
                   );

10,000 rows is not an "extremely long table". In fact, it is pretty small by database standards. If performance is an issue, create an index:

create index idx_t_unit_id_entry_id on t(unit_id, entry_id);

Answer 2

You can use SELF JOIN with GROUP BY, e.g.:

SELECT t.*
FROM table t JOIN 
(SELECT unit_id, MAX(entry_id) AS `entry_id`
FROM table
GROUP BY unit_id
) t1 ON t.entry_id = t1.max_id;