Selecting only the latest version in a group with a specific status

Question

In a project I am working on I have the table you can see below. On the frontend I need to show only the records that are published grouped by the entity_id. For example, in the example below only id 1, 11, 16 and 19 should be shown. I have no idea how to make this query. I tried several things with subqueries etc but none of them work. I guess there should be a way to retrieve this data. What am I missing?

| id | revision   | entity_id   | status     
========================================
| 1  | 1          | 1           | published
| 2  | 2          | 1           | archived
| 3  | 1          | 2           | draft
| 4  | 2          | 2           | draft
| 5  | 3          | 2           | draft
| 6  | 4          | 2           | ready
| 7  | 5          | 2           | draft
| 8  | 6          | 2           | published
| 9  | 7          | 2           | published
| 10 | 8          | 2           | ready
| 11 | 9          | 2           | published
| 13 | 1          | 3           | draft
| 14 | 1          | 4           | draft
| 15 | 2          | 4           | draft
| 16 | 3          | 4           | published
| 18 | 1          | 5           | draft
| 19 | 2          | 5           | published
| 20 | 3          | 5           | draft
| 21 | 10         | 5           | archived

I created a DBFiddle to play around: https://www.db-fiddle.com/f/4UcjKhTvzzNQWL3Pfkfew4/1

Note It's not the same as SQL select only rows with max value on a column since the answer there would select all the revisions that are published and not just the latest one.

which version of MySql are you using? – jeroenh Oct 11 '20 at 16:09 — jeroenh, Oct 11 '20 at 16:09
I am using version 5.7 – Bart Bergmans Oct 11 '20 at 16:18 — Bart Bergmans, Oct 11 '20 at 16:18

score 1 · Answer 1 · answered Oct 11 '20 at 17:01

Presumably you're after something like this...

DROP TABLE IF EXISTS entities;

CREATE TABLE `entities` 
( id SERIAL PRIMARY KEY
, entity_id INT NOT NULL
, revision INT NOT NULL DEFAULT '1'
, type enum('gym','trainer') 
, status enum('published','ready','draft','archived') NOT NULL DEFAULT 'draft'
, UNIQUE KEY entities_entity_id_revision_unique (entity_id,revision)
);


INSERT INTO entities
(id, entity_id, revision, type,status) VALUES
( 1,1, 1,'gym','published'),
( 2,1, 2,'gym','archived'),
( 3,2, 1,'gym','draft'),
( 4,2, 2,'gym','draft'),
( 5,2, 3,'gym','draft'),
( 6,2, 4,'gym','ready'),
( 7,2, 5,'gym','draft'),
( 8,2, 6,'gym','published'),
( 9,2, 7,'gym','published'),
(10,2, 8,'gym','ready'),
(11,2, 9,'gym','published'),
(13,3, 1,'gym','draft'),
(14,4, 1,'gym','draft'),
(15,4, 2,'gym','draft'),
(16,4, 3,'gym','published'),
(18,5, 1,'gym','draft'),
(19,5, 2,'gym','draft'),
(20,5, 3,'gym','draft'),
(21,5,10,'gym','published');
    
    
SELECT x.* 
  FROM entities x
  JOIN 
     ( SELECT entity_id
            , MAX(revision) revision
         FROM entities
        WHERE status = 'published'
        GROUP
           BY entity_id
     ) y
    ON y.entity_id = x.entity_id
   AND y.revision = x.revision;
   
   +----+-----------+----------+------+-----------+
   | id | entity_id | revision | type | status    |
   +----+-----------+----------+------+-----------+
   |  1 |         1 |        1 | gym  | published |
   | 11 |         2 |        9 | gym  | published |
   | 16 |         4 |        3 | gym  | published |
   | 21 |         5 |       10 | gym  | published |
   +----+-----------+----------+------+-----------+
   4 rows in set (0.00 sec)

I think this is exactly what I need! Thanks! Is there also a way to remove the entire entity from the result set when the latest revision status is archived? — Bart Bergmans, Oct 11 '20 at 17:05
If 'archived' is always the latest revision (when it occurs) then it's trivial. — Strawberry, Oct 11 '20 at 17:11

nbk · Answer 2 · 2020-10-11T18:12:29.603

It s almost the same as with the max value

Query #1

SELECT * 
FROM
`entities`
WHERE id IN
(SELECT MIN(id)id FROM
`entities`
WHERE  `status` = 'published'
GROUP BY `entity_id`);

| id  | entity_id | revision | type | status    | name | slug | short_description | description | address | phone | email | website | openinghours | images | thumbnail | pricerange | created_at          | updated_at          | deleted_at          |
| --- | --------- | -------- | ---- | --------- | ---- | ---- | ----------------- | ----------- | ------- | ----- | ----- | ------- | ------------ | ------ | --------- | ---------- | ------------------- | ------------------- | ------------------- |
| 1   | 1         | 1        | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-03 21:49:14 | 2020-10-03 21:49:14 |                     |
| 8   | 2         | 6        | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-10 16:28:14 | 2020-10-10 16:28:15 |                     |
| 16  | 4         | 3        | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-10 17:06:38 | 2020-10-10 17:06:53 | 2020-10-10 17:06:53 |
| 21  | 5         | 10       | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-11 14:54:16 | 2020-10-11 14:54:16 |                     |

View on DB Fiddle

If you have multiple rivision you can use max, as the id is always increase by every new revision this makes no difference at all

Query #1

SELECT * 
FROM
`entities`
WHERE (`entity_id` ,`revision`) IN
(SELECT `entity_id` ,MAX(`revision`)  FROM
`entities`
WHERE  `status` = 'published'
GROUP BY `entity_id`);

| id  | entity_id | revision | type | status    | name | slug | short_description | description | address | phone | email | website | openinghours | images | thumbnail | pricerange | created_at          | updated_at          | deleted_at          |
| --- | --------- | -------- | ---- | --------- | ---- | ---- | ----------------- | ----------- | ------- | ----- | ----- | ------- | ------------ | ------ | --------- | ---------- | ------------------- | ------------------- | ------------------- |
| 1   | 1         | 1        | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-03 21:49:14 | 2020-10-03 21:49:14 |                     |
| 11  | 2         | 9        | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-10 16:48:20 | 2020-10-10 17:00:47 |                     |
| 16  | 4         | 3        | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-10 17:06:38 | 2020-10-10 17:06:53 | 2020-10-10 17:06:53 |
| 21  | 5         | 10       | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-11 14:54:16 | 2020-10-11 14:54:16 |                     |

View on DB Fiddle

Is it possible to get something like this but not based on is but on revision — Bart Bergmans, Oct 11 '20 at 16:19
If you have multiple rivisions you can use max, as the id is always increase by every new revision this makes no difference at all and the other query is simpler — nbk, Oct 11 '20 at 18:13

score 0 · Answer 3 · answered Oct 11 '20 at 17:18

You can also use over partition by.

SELECT * FROM(
SELECT *,
  ROW_NUMBER() OVER( PARTITION BY ENTITY_ID ORDER BY REVISION ASC) AS RN 
  FROM ENTITIES
WHERE STATUS = 'PUBLISHED') K WHERE RN =1



| id  | entity_id | revision | type | status    | name | slug | short_description | description | address | phone | email | website | openinghours | images | thumbnail | pricerange | created_at          | updated_at          | deleted_at          | RN  |
| --- | --------- | -------- | ---- | --------- | ---- | ---- | ----------------- | ----------- | ------- | ----- | ----- | ------- | ------------ | ------ | --------- | ---------- | ------------------- | ------------------- | ------------------- | --- |
| 1   | 1         | 1        | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-03 21:49:14 | 2020-10-03 21:49:14 |                     | 1   |
| 8   | 2         | 6        | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-10 16:28:14 | 2020-10-10 16:28:15 |                     | 1   |
| 16  | 4         | 3        | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-10 17:06:38 | 2020-10-10 17:06:53 | 2020-10-10 17:06:53 | 1   |
| 21  | 5         | 10       | gym  | published |      |      |                   |             |         |       |       |         |              |        |           |            | 2020-10-11 14:54:16 | 2020-10-11 14:54:16 |                     | 1   |

View on DB Fiddle

Selecting only the latest version in a group with a specific status

3 Answers3