How to filter rows based on columns with maximum value?

Question

I have a table as below

--------Table 1----------
ID     VERSION     STATUS
001       1          1
001       1          2
002       1          3
002       2          4
002       2          3

So here I need to filter the ID's based on the max value of version and status. The expected result is below

--------Table 1----------
ID     VERSION     STATUS
001       1          2
002       2          4

I tried using the code

select * 
from "table1" 
where "VERSION" = (select max("VERSION") from  "table1") 
  and "STATUS" = (select max("STATUS") from "table1")

But it is not working. Please help

Tag your question with the database you are using. Do you have a unique id on each row? — Gordon Linoff, Jun 28 '19 at 12:10
What in cases of "ties" where the status MAX() value is the same in the ID, VERSION group? Do you need both records or just a single.. — Raymond Nijland, Jun 28 '19 at 12:38

score 1 · Answer 1 · answered Jun 28 '19 at 12:10

1

Unless I'm missing something obvious, a simple group by with max should do it:

SELECT ID, MAX(VERSION) As Version, MAX(STATUS) As Status
FROM table1
GROUP BY ID

answered Jun 28 '19 at 12:10

Zohar Peled

79,642
10
69
121

indeed i also was wondering about the window function answers, Topicstarter you would most like have to add a `ORDER BY ID` as MySQL 8 does not sort GROUP BY annymore.. – Raymond Nijland Jun 28 '19 at 12:45

score 1 · Answer 2 · answered Jun 28 '19 at 12:11

1

select id,max(version),max(status) from 
table1
group by id

answered Jun 28 '19 at 12:11

Kedar Limaye

1,041
8
15

Topicstarter you would most like have to add a `ORDER BY ID` as MySQL 8 does not sort GROUP BY annymore.. – Raymond Nijland Jun 28 '19 at 12:44

score 0 · Answer 3 · answered Jun 28 '19 at 12:08

0

most dbms support row_number(), you can try like below

   select * from 
    (select *,row_number() over(partition by id order by status desc) rn
    from table_name
    ) a where a.rn=1

answered Jun 28 '19 at 12:08

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 4 · answered Jun 28 '19 at 12:10

0

You can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by id order by version desc, status desc) as seqnum
      from t
     ) t
where seqnum = 1;

answered Jun 28 '19 at 12:10

Gordon Linoff

1,242,037
58
646
786

Red Devil · Answer 5 · 2019-06-28T12:17:05.037

0

Try this:

select id,version,status from (
select *,ROW_NUMBER() over (partition by id order by status desc) rn from YOURTABLE
) t
where rn=1

edited Jun 28 '19 at 12:17

answered Jun 28 '19 at 12:13

Red Devil

2,343
2
21
41

the SQL tag is about writing ANSI/ISO SQL standard code not about SQL Server (MSSQL) – Raymond Nijland Jun 28 '19 at 12:14

How to filter rows based on columns with maximum value?

5 Answers5