MYSQL | Get rows which has maximum column value within a group belonging to a particular status-CodePudding

Schema Details

We are maintaining collections data in a project. Main columns of Collections table are id(INT), collectionId(String UUID), versionNo(INT), status(PUBLISHED/NEW/PURCHASED/DELETED/ARCHIVED). Each collection can have several versions. For each different version, versionNo, id, status column will have different values but collectionId will be same.

Sample Data

id     collectionId                            versionNo   status
5   17af2c88-888d-4d9a-b7f0-dfcbac376434    1       PUBLISHED
80  17af2c88-888d-4d9a-b7f0-dfcbac376434    2       PUBLISHED
109 17af2c88-888d-4d9a-b7f0-dfcbac376434    3       NEW
6   d8451652-6b9e-426b-b883-dc8a96ec0010    1       PUBLISHED

Problem Statement

We want to fetch details of highest published version of collections. For example: for above dataset desired output is

id     collectionId                            versionNo   status
80  17af2c88-888d-4d9a-b7f0-dfcbac376434    2       PUBLISHED
6   d8451652-6b9e-426b-b883-dc8a96ec0010    1       PUBLISHED

We tried following queries but either getting duplicate entries or not getting collections with single version only:

select * from Collections where status="PUBLISHED" group by collectionId having versionNo=max(versionNo);
select T1.* from Collections T1 inner join Collections T2 on T1.collectionId = T2.collectionId AND T1.id <> T2.id where T1.status="PUBLISHED" AND T1.versionNo > T2.versionNo;

UPDATE: I am using MYSQL version 5.7.12.

CodePudding user response：

In order to get the highest version you have to compare collection data with itself using the join as follows

select C1.* from Collections C1 
   left join Collections C2 on C1.collectionId = C2.collectionId 
   AND C1.status="PUBLISHED" AND C2.status="PUBLISHED" 
   AND C1.versionNo < C2.versionNo 
   WHERE C2.versionNo IS NULL 
   AND C1.status="PUBLISHED"

CodePudding user response：

Use ROW_NUMBER() assuming you are running MySQL 8 :

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY collectionId ORDER BY versionNo DESC) rn
    FROM Collections
    WHERE status = 'PUBLISHED'
)

SELECT id, collectionId, versionNo, status
FROM cte
WHERE rn = 1;