Aggrgating Group Query (list newest Entry out of Group of same id )

Question

Version:mysql Ver 14.14 Distrib 5.7.25, for Linux

Table articles:

id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(120) NOT NULL,
  `creator` varchar(45) NOT NULL,
  `versionid` varchar(45) NOT NULL,
  `content` mediumtext NOT NULL,
  `category` varchar(45) NOT NULL,
  `publishdate` varchar(120) NOT NULL,
  `state` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)

When an article is updated he gets a new id, but versionid is the same. I can't find a query to Select all articles, but only the newest of those, which have the same versionid.

score 1 · Answer 1 · answered May 09 '19 at 06:28

1

use correlated subquery

    select t1.* from table_name t1
    where t1.id= ( select max(id) 
                    from table_name t2 where t1.versionid=t2.versionid
                 )

answered May 09 '19 at 06:28

Zaynul Abadin Tuhin

31,407
5
33
63

1

You are my hero! :) – Paul May 09 '19 at 06:31
Correlated subquery is slow like hell. It runs N+1 queries, one query for each row. – gaborsch May 09 '19 at 06:32
@gaborsch have you checked the query plan of your query and my query :) – Zaynul Abadin Tuhin May 09 '19 at 06:37
@ZaynulAbadinTuhin Yes, I did. Yours is using a DEPENDENT SUBQUERY, my query is using a derived table. – gaborsch May 09 '19 at 06:46
If you have a question, which one is better, check this: https://stackoverflow.com/questions/17268848/difference-between-subquery-and-correlated-subquery/21036822#21036822 – gaborsch May 09 '19 at 06:50

Aggrgating Group Query (list newest Entry out of Group of same id )

1 Answers1