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
– Gordon Linoff, Commented Jun 28, 2019 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
– Raymond Nijland, Commented Jun 28, 2019 at 12:38

Zohar Peled · Accepted Answer · 2019-06-28 12:10:53Z

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, 2019 at 12:10

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raymond Nijland Over a year ago

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..

Kedar Limaye · Accepted Answer · 2019-06-28 12:11:10Z

1

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

answered Jun 28, 2019 at 12:11

Kedar Limaye

1,0418 silver badges15 bronze badges

1 Comment

Raymond Nijland Over a year ago

Topicstarter you would most like have to add a ORDER BY ID as MySQL 8 does not sort GROUP BY annymore..

Zaynul Abadin Tuhin · Accepted Answer · 2019-06-28 12:08:17Z

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, 2019 at 12:08

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2019-06-28 12:10:26Z

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, 2019 at 12:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Red Devil · Accepted Answer · 2019-06-28 12:17:05Z

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, 2019 at 12:17

answered Jun 28, 2019 at 12:13

Red Devil

2,4135 gold badges30 silver badges46 bronze badges

1 Comment

Raymond Nijland Over a year ago

the SQL tag is about writing ANSI/ISO SQL standard code not about SQL Server (MSSQL)

Collectives™ on Stack Overflow

How to filter rows based on columns with maximum value?

5 Answers 5

1 Comment

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related