Returning specific rows by grouping columns

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.

version(revisionNumber)

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.

MAX(revisionNumber)

id

effectiveDate

3 Answers
3

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
)


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

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

GROUP BY

JOIN

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


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.