CODEWITHSUNDEEP
phpmysqlsql
Aitor Chicharro
Aitor Chicharro

Reputation: 73

select the database, getting all the maximum values of a column

I have the following table:

id | value | data | v
1  | val1  | dat1 | 1
2  | val1  | dat2 | 2
3  | val1  | dat3 | 3
4  | val2  | dat4 | 1

What I do is grab the data, each value, which has higher v. No what I mean ..

Sql output I would like:

id | value | data | v
3  | val1  | dat3 | 3
4  | val2  | dat4 | 1

Upvotes: 0

Views: 108

Answers (2)

Yaroslav
Yaroslav

Reputation: 6534

As gillyspy already commented, what you need is a subquery that returns the correct values. Check this code:

SELECT id, table1.value, data, v
  FROM Table1
  JOIN (SELECT MAX(v) MAXV, value
          FROM Table1 
         GROUP BY value
       ) T ON T.MAXV = Table1.v
           AND T.value = Table1.value;

Upvotes: 0

gillyspy
gillyspy

Reputation: 1598

You need to identify the max value in a subquery and then join against the constant element

Fiddle

select * 
from 
    Table1  
       join 
   (select max(v) MAXV, value from Table1 group by value) T 
         on T.MAXV = Table1.v and T.value=Table1.value

Upvotes: 4

Related Questions