CODEWITHSUNDEEP
mysqldatabase
Captain Nemo
Captain Nemo

Reputation: 355

MySQL statement to select the latest entry of a specific column

I am using MySQL and the table was created with this schema:

CREATE TABLE `example` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `version` INT UNSIGNED NOT NULL,
  `text` VARCHAR(45) NOT NULL,
  `class_id` INT NOT NULL,
  `tyoe_id` INT NULL,
  PRIMARY KEY (`id`));

The table has the following entries as seen in the link (not allowed to embed).

enter image description here

For a specific class_id (for example 10) I want to get all distinct type_ids with max version. There are 5 type_id 1,2,3,4,5 that may exists or not, however if a type_id exists in a specific class we want the latest (max version). The query should return the text column as well.

In case it is needed, here are the insert statements

INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (1,1,'text1',10,1);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (2,1,'text2',10,2);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (3,1,'test3',10,3);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (4,1,'test4',10,4);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (5,1,'test5',10,5);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (6,2,'test44',10,3);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (7,1,'1111',11,1);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (8,1,'eferwer',12,2);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (9,3,'last',10,3);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (10,2,'new',10,5);
INSERT INTO `example1` (`id`,`version`,`text`,`class_id`,`tyoe_id`) VALUES (11,3,'rrrr',10,5);

Upvotes: 2

Views: 56

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28854

  • In a derived table get the maximum version value for every type_id
  • Join back to main table to get the corresponding row.

Try the following: 

SELECT e.* 
FROM 
example1 AS e 
JOIN 
(
  SELECT type_id, 
         MAX(version) AS maximum_version
  FROM example1 
  WHERE class_id = 10
  GROUP BY type_id 
) AS dt ON dt.type_id = e.type_id AND 
           dt.maximum_version = e.version 
WHERE e.class_id = 10

Result

| id  | version | text  | class_id | type_id |
| --- | ------- | ----- | -------- | ------- |
| 1   | 1       | text1 | 10       | 1       |
| 2   | 1       | text2 | 10       | 2       |
| 4   | 1       | test4 | 10       | 4       |
| 9   | 3       | last  | 10       | 3       |
| 11  | 3       | rrrr  | 10       | 5       |

View on DB Fiddle

Upvotes: 1

Related Questions