youzking youzking - 1 month ago 5
SQL Question

oracle - getting highest values for each two type

let's say this is the table "transcations" :



table {
border-collapse: collapse;
}
table,
th,
td {
border: 1px solid black;
}
td{ padding-right: 7px; padding-right: 7px; text-align: center;}

<table align="center">
<tr><th> first_type <th> second_type <th> value
<tr><td> m <td> t1 <td> 2
<tr><td> a <td> t2 <td> 2
<tr><td> a <td> t3 <td> 2
<tr><td> b <td> t1 <td> 6
<tr><td> g <td> t4 <td> 4
<tr><td> b <td> t2 <td> 2
<tr><td> r <td> t4 <td> 3
<tr><td> m <td> t4 <td> 2
<tr><td> g <td> t1 <td> 2
<tr><td> b <td> t4 <td> 6
<tr><td> a <td> t4 <td> 17
</table>





how can i select the max value for each 'first_type' while showing associated 'second_type' column, the desired result is :



table {
border-collapse: collapse;
}
table,
th,
td {
border: 1px solid black;
}
td{ padding-right: 7px; padding-right: 7px; text-align: center;}

<table align="center">
<tr><th> first_type <th> second_type <th> value
<tr><td> m <td> t1 <td> 2
<tr><td> b <td> t1 <td> 6
<tr><td> g <td> t4 <td> 4
<tr><td> r <td> t4 <td> 3
<tr><td> m <td> t4 <td> 2
<tr><td> b <td> t4 <td> 6
<tr><td> a <td> t4 <td> 17
</table>





or keeping just the highest value omitting other rows that have the same value for the 'first_type' column, like this :



table {
border-collapse: collapse;
}
table,
th,
td {
border: 1px solid black;
}
td{ padding-right: 7px; padding-right: 7px; text-align: center;}

<table align="center">
<tr><th> first_type <th> second_type <th> value
<tr><td> m <td> t1 <td> 2
<tr><td> g <td> t4 <td> 4
<tr><td> r <td> t4 <td> 3
<tr><td> b <td> t4 <td> 6
<tr><td> a <td> t4 <td> 17
</table>





i tried to select the max value while grouping by 'first_type' but i can't select 'second type since oracle does not permit selecting something not in the group clause.

thanks in advance.

Answer

you can use a where in with max

select first_type, second_type, value 
from my_table 
where ( first_type, value) in (select first_type, max(value)
                                from my_table 
                                group by first_type)
Comments