Skip to content
Advertisement

find the max for each value in SQL

i have tables like this

table 1

|cl.1| 
| -- | 
| a  | 
| b  |
| c  |

table 2

|cl.1|cl.2|para|
|----|---| --- |
| a  | 3 |  t  |
| a  | 3 |  f  |
| b  | 2 |  t  |
| a  | 1 |  b  |
| c  | 4 |  t  |
| b  | 7 |  d  |

i want to get the max value for each element in table1 from table2 and the different parameter so the expecited tabel should be like this

|cl.1|max|para|
|----|---| --- |
| a  | 3 |  t  |
| a  | 3 |  f  |
| c  | 4 |  t  |
| b  | 7 |  d  |

Advertisement

Answer

You can try to compute all the maximums:

with Maxes as (
    select cl1,
           max(cl2) as cl2
      from Table2
  group by cl1)

and then join them with the original Table2, e.g.

with Maxes as (
    select cl1,
           max(cl2) as cl2
      from Table2
  group by cl1)

select t.*
  from Table2 t join
       Maxes m on (t.cl1 = m.cl1 and t.cl2 = m.cl2)
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement