0

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Russ Back
  • 903
  • 2
  • 15
  • 27
  • Add a boolean column that is marked if a record is last. So you would be able to select last rows without `GROUP BY` at all. – zerkms Apr 16 '14 at 22:24
  • possible duplicate of [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) – Bill Karwin Apr 16 '14 at 22:47

1 Answers1

1

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;
Ikan
  • 71
  • 3