0

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
SELECT id
     , SUBSTRING(MAX(time || name) FROM 11) "name"
     , SUBSTRING(MAX(time || title) FROM 11) title
     , MAX(time) "time"
FROM test_table
GROUP BY id

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 Jul 5 '18 at 10:54
  • What does the "from 11" bit do? – RedM Jul 5 '18 at 11:55
  • @RedM Function extracts data (name, title) field from aggregate. First 10 symbols is date (time field). – Akina Jul 5 '18 at 12:37
  • Hmm.. somebody downvoted the answer without both reason explaination and posting another, the better, answer. Modest, maybe... – Akina Jul 5 '18 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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