CODEWITHSUNDEEP
sqloracle-databaseselect
rofans91
rofans91

Reputation: 3010

Oracle SQL Return Single Row Based On Max Value of a Column

I have in my database table

a | 1

a | 2

a | 3

a | 4

How should I return only with a SELECT query:

a | 4

I have tried several combination with distinct and max values but all seem to be irrelevant.

Upvotes: 2

Views: 58098

Answers (7)

Sarathi Kamaraj
Sarathi Kamaraj

Reputation: 697

If the a you have given are values in the table then you can use

select column_name, max(column_b) from table_name

Upvotes: -3

Sumukh
Sumukh

Reputation: 749

I am assuming you dont just want to get a|4 but there are other similar combinations like b|3, c|6 etc, So assuming the columns are c1, c2, the query would be

select * from table_1 where (c2) in (select max(c2) from table_1 group by c1)

If your table is like 

C1|C2
a|1
a|2
a|4
b|3
c|2
c|6

the output would be like

a|4
b|3
c|6

Upvotes: 4

Hamidreza
Hamidreza

Reputation: 3118

the query is this:

select * from yourTable 
where B = (select max(B) from yourTable);

Upvotes: 6

rav
rav

Reputation: 11

Try like this.

select Col_A,Col_B
from Table_X
where Col_B =(select max(Col_B) from Table_X)

Upvotes: 1

Anshul Tiwari
Anshul Tiwari

Reputation: 643

Assuming your table T has two columns C1 and C2, have you tried it following way?

select C1, C2 from T where C2 in (select max(C2) from T)

EDIT - I tried mine as well as other suggested answers, and to my surprise, this one is working best for me, even though I have to do full table scan in the sub-query. If you've got what you were looking for, could you please share it with us, or mark the best answer?

Upvotes: 1

koljaTM
koljaTM

Reputation: 10262

SELECT MAX(a) AS a FROM <TABLE>

Edit: I thought "a" was the name of the column, if it is another column, use

SELECT col1, MAX(col2) FROM <TABLE> GROUP BY col1

which will return one row per col1 value. If there are other values there (like b, c), it depends on what you want.

Upvotes: 7

Frank Schmitt
Frank Schmitt

Reputation: 30765

You can use analytic functions for this (assuming the name column contains the 'a' and the value column contains 1,2,3,4, ...):

select * from (
  select name, value, rownum over (partition by 1 order by value desc) 
  as rn
  from mytable)
where rn = 1

Or, you can use a plain old ORDER BY:

select * from (
  select name, value
  from mytable
  order by value desc)
where rownum = 1

Upvotes: 2

Related Questions