CODEWITHSUNDEEP
sqlsql-server
user13948043
user13948043

Reputation:

Best way to group records with MAX revision

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.

Upvotes: 0

Views: 79

Answers (2)

O. Jones
O. Jones

Reputation: 108686

You have a correct query (if I understand your requirement). You are very close to an ideal query. But your outer WHERE clause contains a correlated (dependent) subquery, and those don't always perform well.

You can think of this as the JOIN of two subqueries. The one you have.

              SELECT id,
                     MAX(revision) AS revision
                FROM table_a
                WHERE status = 'APPROVED'
                GROUP BY id

and this one.

              SELECT id,
                     MAX(revision) AS revision
                FROM table_a
                GROUP BY id

You FULL JOIN them together. Like this.

SELECT max.id, 
       latest.revision as latest_rev,
       approved.revision as approved_rev
  FROM (
              SELECT id,
                     MAX(revision) AS revision
                FROM table_a
                GROUP BY id
       ) latest
 FULL JOIN (
              SELECT id,
                     MAX(revision) AS revision
                FROM table_a
                GROUP BY id
            ) approved on latest.id = approved.id

In this case you can actually use LEFT JOIN because you know every id in the approved subquery is also present in the latest subquery.

Upvotes: 0

Thom A
Thom A

Reputation: 95569

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;

Upvotes: 1

Related Questions