Postgresql – Combine rows to remove nulls while using latest value based on time

distinctgroup bypostgresqlquery

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

Best Answer

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).

Related Solutions

Mysql – Select rows based on latest date with multiple joins

Your query can be simplified to:

SELECT
    c.id,
    c.name,
    a.rtime,
    s.status_name,
    u.user_name
FROM company c
    LEFT JOIN
      ( SELECT 
            company_id,
            MAX(rtime) AS maxdate
        FROM action
        WHERE deleted IS NULL
        GROUP BY company_id
      ) AS x ON x.company_id = c.id
    LEFT JOIN action a ON  a.deleted IS NULL
                       AND a.company_id = x.company_id 
                       AND a.rtime = x.maxdate 
    LEFT JOIN user u ON u.id = a.user_id
    LEFT JOIN status s ON s.id = a.status_id
WHERE 
    c.name LIKE 'Company%' ;

An index on (deleted, company_id, rtime) would make the derived table subquery efficient. I suppose you already have indexes on the columns used for the joins and on the Company (name).

SQL-Fiddle

Postgresql – how to use index to speed up sorting in postgres

Query

This query should be substantially faster in any case:

SELECT parent_id, message_id, posted_at, share_count
FROM   messages
WHERE  feed_id = 7
AND    posted_at >= '2015-01-01 4:0:0'
AND    posted_at <  '2015-04-28 4:0:0'
AND    parent_id IS NULL  -- match index condition
UNION ALL
(
SELECT DISTINCT ON(parent_id)
       parent_id, message_id, posted_at, share_count
FROM   messages
WHERE  feed_id = 7
AND    posted_at >= '2015-01-01 4:0:0'
AND    posted_at <  '2015-04-28 4:0:0'
AND    parent_id IS NOT NULL  -- match index condition
ORDER  BY parent_id, posted_at DESC NULLS LAST
)
ORDER  BY share_count DESC NULLS LAST, posted_at DESC NULLS LAST;

The CTE does nothing here that a plain subquery could not deliver also. And a CTE introduces an optimization barrier since it is executed separately and its result is materialized.
You have one more subquery-level than you actually need.
The expression (COALESCE(parent_id, message_id) is not compatible with a plain index, you would need an index on that expression. But that may not be very useful either, depending on data distribution. Follow my links below for detailed information.
Splitting the simple case of parent_id IS NULL into a separate SELECT may or may not deliver the optimum. Especially not, if that's a rare case anyway, in which case a combined query with an index on (COALESCE(parent_id, message_id) may perform better. Other considerations apply ...

Indices

Especially when supported with these indices:

CREATE INDEX messages_idx_null ON messages (
  feed_id
, posted_at DESC NULLS LAST
, share_count DESC NULLS LAST
, parent_id, message_id
)
WHERE parent_id IS NULL;

CREATE INDEX messages_idx_notnull ON messages (
  feed_id
, posted_at DESC NULLS LAST
, share_count DESC NULLS LAST
, parent_id, message_id
)
WHERE parent_id IS NOT NULL;

The two partial indices cover the whole table together and are about the same size together as a single total index.

The last two columns parent_id, message_id only make sense if you get index-only scans out of it. Else remove them from both indices.

SQL Fiddle.

Depending on missing details, DISTINCT ON may or may not be the best query technique for the purpose. Reade detailed explanation here:

Select first row in each GROUP BY group?

And possibly faster alternatives here:

Optimize GROUP BY query to retrieve latest record per user