CODEWITHSUNDEEP
mysqlsql
Tum
Tum

Reputation: 3652

How to query to get the Max value of a field in relating to another field? Group By Doesn't work? (MySQL)

I have a table with 2 columns: EntryID and its Version. Each entry can have many different version, the highest version is the latest version of that entry. Also each entryID may have more than 1 name

EntryID - Version - EntryName
212 - 1 - Car
212 - 1 - Car2
212 - 2 - Batr
212 - 2 - hoo
451 - 2 - Csert
451 - 3 - xxx
451 - 3 - xxx2
111 - 1 - yyy
333 - 4 - ggg

Now, based on the entryID provided, I have a need to get the all the entries that have max versions only.

Ex, the user may enter 212 & 451 & hit the button then it will show:

EntryID - Version - EntryName
212 - 2 - Batr
212 - 2 - hoo
451 - 3 - xxx
451 - 3 - xxx2

The below query using group by but doesn't work.

 select * from table where entryID in (212,451) and version in 
(select max(version) from table where entryID in (212,451) group by entryID)

Result:

EntryID - Version - EntryName
212 - 2 - Batr
212 - 2 - hoo
451 - 2 - Csert
451 - 3 - xxx
451 - 3 - xxx2

This is not correct because entry 451 included the version 2 which is the max version of entry 212.

Upvotes: 0

Views: 71

Answers (3)

mdatwood
mdatwood

Reputation: 98

select *
from test_table a
where exists (select 1 
    from test_table b 
    where a.id = b.id 
    having max(b.version) = a.version)

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try:

select y.*
  from y.table y
 where y.entryid in (212, 451)
   and y.version =
       (select max(x.version) from table x where x.entryid = y.entryid)

Upvotes: 1

Barmar
Barmar

Reputation: 780724

SELECT t1.*
FROM Table t1
JOIN (SELECT EntryID, MAX(version) maxversion
      FROM Table
      WHERE EntryID IN (212, 451)
      GROUP BY EntryID) t2
ON t1.EntryID = t2.EntryID AND t1.version = t2.maxversion

Upvotes: 1

Related Questions