Imagine you have the following setup:

CREATE TABLE namechanges (
  id text,
  new_name text,
  change_date timestamp

  ('x', 'alice', '2020-03-01'),
  ('y', 'Bob T.', '2020-03-03'),
  ('x', 'Alice', '2020-03-05'),
  ('z', 'charlie', '2020-03-07'),
  ('x', 'Alice C.', '2020-03-09'),
  ('z', 'Charlie Z.', '2020-03-11')

How would look like a query that would retrieve just the current name for each id and return the following?

| id  | max        |
| --- | ---------- |
| z   | Charlie Z. |
| y   | Bob T.     |
| x   | Alice C.   |

Here's the example above on DB Fiddle if you want to play with it: https://www.db-fiddle.com/f/ugNcXhRyb44KTpqjFXmKDW/0

3 Answers 3


A much better way of doing what you want to do is to use the ROW_NUMBER() Analytic function as follows (see fiddle here). It avoids the "workaround" of concatenating strings and these functions are great - see below.

SELECT rn, id, new_name, change_date 
    ROW_NUMBER() OVER (PARTITION BY id  ORDER BY id, change_date DESC) as rn,
    id, new_name, change_date
  FROM namechanges
  ORDER  BY id, change_date DESC
) AS tab
WHERE rn = 1
ORDER BY id, change_date;


rn  id  new_name    change_date
1   x   Alice C.    2020-03-09 00:00:00
1   y   Bob T.      2020-03-03 00:00:00
1   z   Charlie Z.  2020-03-11 00:00:00

Analytic (aka Window) functions are very powerful and will reward, a gazillion fold, time spent learning them! I've left the steps in the logic that I followed in the fiddle, so that (plus the tutorial link above) should give you a good start.


In Postgres this can be solved using distinct on ()

select distinct on (id) *
from namechanges
order by id, change_date desc;

This is typically faster than using aggregation or Window functions.

Online example

  • True - your solution - Planning Time: 0.155 ms Execution Time: 0.118 ms and [mine]( dbfiddle.uk/…) - Planning Time: 0.222 ms Execution Time: 0.281 ms - yours is ~ > 50% better! Nice one (+1) but, my solution is standards compliant! :-)
    – Vérace
    Mar 16, 2020 at 11:31
  • @Vérace: yes, absolutely. It's also more versatile as it can easily be extended to "the two most recent changes" or similar variatioins.
    – user1822
    Mar 16, 2020 at 11:42

I've found a workaround that seems to work for the case above on PostgreSQL:

SELECT id, max((change_date || '-' || new_name))
FROM namechanges

That will return the last name, although prefixed with the date. I'm not sure if this is the correct way of doing it and how performant it is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.