Best way to group records with MAX revision-CodePudding

I have a source table like this:

table_a :

id	revision	status
1	0	APPROVED
1	1	PENDING

I am trying to get distinct records from table_a having the latest revision and show the latest approved revision for each one of them.

result table_b :

id	latest_rev	latest_approved_rev
1	1	0

I have written the following query :

SELECT a.id,
       a.revision AS latest_rev,
       b.latest_approved_rev
FROM table_a a
     LEFT JOIN (SELECT id,
                       MAX(revision) AS latest_approved_revision
                FROM table_a
                WHERE status = 'APPROVED'
                GROUP BY id) b ON b.id = a.id
WHERE a.revision = (SELECT MAX(revision)
                    FROM table_a
                    WHERE id = a_id

My query seems to work fine, but I am wondering if I was missing something and/or if there is another way to make the query better/faster.

CodePudding user response：

Seems you could achieve this with some (conditional) aggregation:

SELECT id,
       MAX(revision) AS latest_rev,
       MAX(CASE status WHEN 'APPROVED' THEN revision END) AS latest_approved_rev
FROM (VALUES(1,0,'APPROVED'),
            (1,1,'PENDING'))V(id,revision,status)
GROUP BY id;

CodePudding user response：

Try this:

SELECT 
    id, 
    revision latest_rev, 
    status   latest_approved_rev    
FROM table_a a
INNER JOIN (
    SELECT 
        id, MAX(revision) revision 
    FROM table_a
    GROUP BY id
) b ON a.id = b.id AND a.revision= b.revision