Probably a very simple SQL question...

Tom Dunne

You know the story, working on something all day, got so near, yet so far from the final solution.

I have distilled data in my Oracle table down to two columns, as follows: (note: this isn't the actual data, this is the resultset from a much larger query)

Value Text
100 String100
103 String103
103 String103
109 String109
110 String110
111 String111
111 String111

Now, I want to get the Text, where the Value column is the max (i.e. 111 in this case).

It's probably something stupidly simple, and on a day where my head wasn't as frazzled, I would probably come up with the answer, but today I don't seem to be able to do that.

I've tried SELECT MAX(value), text from <above> group by value, text but I get multiple TEXT values returned. I just want String111, nothing else.

Any suggestions?

nialo

SELECT TOP (1) MAX(value) AS Expr1, text
FROM <above>
GROUP BY value, text
ORDER BY Expr1 DESC

Beano

nialo wrote: »

SELECT TOP (1) MAX(value) AS Expr1, text
FROM <above>
GROUP BY value, text
ORDER BY Expr1 DESC

Oracle doesnt have top.

why not try

select value, text
from <above>
where value= (select max(value) from <above>)

Beano

Beano wrote: »

Oracle doesnt have top.

why not try

select value, text
from <above>
where value= (select max(value) from <above>)

hmm. reading the op again you only want one value returned

try

select text
from <above>
where rownum = 1
order by value desc

Tom Dunne

Thanks for the suggestions, but in the end, I did a bit of jiggery-pokery with the initial query that generated the data above and got it do do the hard work.

Briefly, I did away with the VALUE colum, broke the TEXT column out into sub-components as follows:

String103 becomes String,1,0,3

And did a sort, descending on 1,0,3, and added a WHERE ROWNUM=1 to get the highest value.

Messy, but I got it working.

MOH

select distinct text, value        
from <table>
where value =                     
(select max(value) from <table>)