I have a table that has multiple rows associated with each ID but sometimes containing differing info:

Key, ID,  Name,  Title,  Time
4,   AA,  Abe,   NULL,   2018-01-05
3,   BB,  NULL,  Mrs,    2018-01-04
2,   AA,  NULL,  Mr,     2018-01-03
1,   BB,  Cat,   Ms,     2018-01-02

Some of the fields are Nulls, some of the row have differing info for the same columns and ID but there will always be an ID and a timestamp.

I want a query where I can squash Nulls but when there are multiple differing fields choose the latest one. Something like this:

Result = 
ID,  Name,  Title,  Time
AA,  Abe,   Mr,     2018-01-05
BB,  Cat,   Mrs,    2018-01-04 

I have something using "SELECT DISTINCT ON (id, name, title)" but I'm missing the next step so it's not working.

SQL Fiddle: http://sqlfiddle.com/#!17/72ba3/1

1 Answer 1

     , SUBSTRING(MAX(time || name) FROM 11) "name"
     , SUBSTRING(MAX(time || title) FROM 11) title
     , MAX(time) "time"
FROM test_table

The FROM 11 part extracts the data (name or title) from the concatenated aggregate values. First 10 characters is the date (time column).

  • @RedM query refined
    – Akina
    Commented Jul 5, 2018 at 10:54
  • What does the "from 11" bit do?
    – RedM
    Commented Jul 5, 2018 at 11:55
  • @RedM Function extracts data (name, title) field from aggregate. First 10 symbols is date (time field).
    – Akina
    Commented Jul 5, 2018 at 12:37
  • Hmm.. somebody downvoted the answer without both reason explaination and posting another, the better, answer. Modest, maybe...
    – Akina
    Commented Jul 5, 2018 at 12:42

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.