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.
7 Answers
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.
the query is this:
select * from yourTable
where B = (select max(B) from yourTable);
Comments
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
Comments
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
Comments
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?
Comments
Try like this.
select Col_A,Col_B
from Table_X
where Col_B =(select max(Col_B) from Table_X)
Comments
If the a you have given are values in the table then you can use
select column_name, max(column_b) from table_name
aconstant, or do you expect other values there, too? Otherwise, you can just doselect 'a', max(b) from table. Also, if there are other values, do you want to see them, or just the one with the max value?