Returning specific rows by grouping columns

Question

I'm stuck with the SQL query, the idea is that I have a database where I keep all the documents with every document version(revisionNumber) etc. What I want to achieve is that I want to access currently only those documents with the latest revisionNumber.

|  id | title               | documentForm | revisionNumber | effectiveDate |
| --: | ------------------- | -------------| -------------: | :------------ |
|   1 | Event Calendar      | SOP-CL       |            1.0 | 2011-02-02    |
|   2 | Event Calendar      | SOP-CL       |            2.0 | 2012-12-16    |
|   3 | Event Calendar      | SOP-CL       |            3.0 | 2014-02-15    |
|   4 | Event Calendar      | SOP-CL       |            4.0 | 2014-08-01    |
|   5 | Event Calendar      | SOP-CL       |            5.0 | 2016-09-12    |
|   6 | Event Calendar      | SOP-CL       |            6.0 | 2018-09-11    |
|   7 | Software development| SOP-DEV      |            1.0 | 2015-11-25    |
|   8 | Granting and...     | SOP-GRA      |            1.0 | 2014-08-04    |
|   9 | Granting and...     | SOP-GRA      |            2.0 | 2015-12-07    |
|  10 | Granting and...     | SOP-GRA      |            3.0 | 2018-03-26    |

And here you can see the result I need to get after query:

|  id | title               | documentForm | revisionNumber | effectiveDate |
| --: | ------------------- | ------------ | -------------: | :------------ |
|   6 | Event Calendar      | SOP-CL       |            6.0 | 2018-09-11    |
|   7 | Software development| SOP-CL       |            1.0 | 2015-11-25    |
|   3 | Granting and...     | SOP-GRA      |            3.0 | 2018-03-26    |

I've been searching in google and found that it can be done by grouping - for example - documentForm and returning MAX(revisionNumber) but I don't get the correct row id and effectiveDate. I guess I just don't use them right.

i think you're looking for this https://stackoverflow.com/a/121450/2845389 — Kaushik, Sep 12 '18 at 06:59

score 2 · Accepted Answer · answered Sep 12 '18 at 06:58

2

use corelated sub-query

select * from table1 t1
where revisionNumber in ( select max(revisionNumber) 
   from
   table1 t2 where t1.title=t2.title and t1.documentForm=t2.documentForm
   group by t2.title,t2.documentForm
   )

answered Sep 12 '18 at 06:58

Zaynul Abadin Tuhin

31,407
5
33
63

Thank you, I've been trying to query similarly but didn't work, but your solution works now! Thanks!! – Sangsom Sep 12 '18 at 07:03

D-Shih · Answer 2 · 2018-09-12T07:06:46.587

You can try to use sub-query in where clauses.

Schema (MySQL v5.6)

CREATE TABLE t (
  title varchar(50),
  documentForm varchar(50), 
  effectiveDate date,
  revisionNumber int

);

insert into t values ('vent Calendar','SOP-CL','2011-02-02',1.0);
insert into t values ('vent Calendar','SOP-CL','2012-12-16',2.0);
insert into t values ('vent Calendar','SOP-CL','2014-02-15',3.0);
insert into t values ('vent Calendar','SOP-CL','2014-08-01',4.0);
insert into t values ('vent Calendar','SOP-CL','2016-09-12',5.0);
insert into t values ('vent Calendar','SOP-CL','2018-09-11',6.0);
insert into t values ('oftware development ','SOP-DEV','2015-11-25',1.0);  
insert into t values ('ranting and..','SOP-GRA','2014-08-04',1.0);
insert into t values ('ranting and..','SOP-GRA','2015-12-07',2.0);
insert into t values ('ranting and..','SOP-GRA','2018-03-26',3.0);

Query #1

SELECT * 
FROM t t1
WHERE revisionNumber  = (
   select max(tt.revisionNumber)
   from t tt
   WHERE t1.documentForm = tt.documentForm
);

| title                | documentForm | effectiveDate | revisionNumber |
| -------------------- | ------------ | ------------- | -------------- |
| vent Calendar        | SOP-CL       | 2018-09-11    | 6              |
| oftware development  | SOP-DEV      | 2015-11-25    | 1              |
| ranting and..        | SOP-GRA      | 2018-03-26    | 3              |

View on DB Fiddle

score 1 · Answer 3 · answered Sep 12 '18 at 06:59

Have a sub-query that does GROUP BY to return each documentForm with its highest version revisionNumber. JOIN with that result:

select t1.*
from tablename t1
join (select documentForm, version(revisionNumber) as maxrevisionNumber
      from tablename
      group by documentForm) t2
    on  t1.documentForm = t2.documentForm
    and t1.revisionNumber = t2.maxrevisionNumber

Returning specific rows by grouping columns

3 Answers3