CODEWITHSUNDEEP
mysqlgroup-bygreatest-n-per-group
Russ Back
Russ Back

Reputation: 923

Ordering data before grouping

I am selecting two fields, 'name' and 'address' from a table of multiple entries. Each record is a home address belonging to each name. A new record is created every time an address is changed and all I need to do is generate a list of names and their latest addresses.

To do this I am selecting name and address grouped by name, which gets me a list of names and addresses. But how can I ensure that the address returned is the latest? There is a date field that I can use but I'm not sure how to sort and group at the same time.

Upvotes: 0

Views: 36

Answers (1)

Ikan
Ikan

Reputation: 71

don't use group by, it's not a good idea, you can tried to use left join like this:

SELECT t1.name, t1.adress
FROM table AS t1 LEFT JOIN table AS t2
ON (t1.name = t2.name AND t1.date < t2.date)
WHERE t2.date IS NULL;

Upvotes: 1

Related Questions