CODEWITHSUNDEEP
sqloracle-databasemax
user10916118
user10916118

Reputation:

Finding max in Oracle

I am looking to apply max function in Oracle SQL on the below example.

CN     10
EX     10
FW     10
CN     11
EX     11
FW     11

I want the result as below.

CN     11
EX     11
FW     11

Appreciate any help. Thanks

Upvotes: 0

Views: 86

Answers (2)

Popeye
Popeye

Reputation: 35910

There are multiple ways of fetching max as follows:

  • MAX aggregate function:

    select col1, max(col2) as max_col2
  from your_table
group by col1

  • Using analytical function:

    select * from
(select t.*, 
        row_number() over (partition by col1 order by col2 desc nulls last) as rn
  from your_table)
where rn = 1

  • Using NOT EXISTS

    select t.*
  from your_table t
 where not exists 
       (select 1 from your_table tt
         where tt.col1 = t.col1 
           and tt.col2 > t.col2)

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Use max() with group by

select col1,max(col2)
from tablename
group by col1

Upvotes: 1

Related Questions