Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Select the row with the highest value
In article <a2ehvu$vnnv3$1_at_ID-115330.news.dfncis.de>, "Kalamaro" says...
>
>Hi. I want to select the row (just one) with the highest value of a given
>column. I mean, if i have the columns "data" and "date", i need to get the
>value of "data" where "date" is the most recent (the highest value of this
>field).
>
>I've tried several ways but they don't work:
>
>"select max(date) from table" -> this selects the newest date, but i want to
>get the value of "data", and if i put
>"select data,max(date) from table" it doesn't work
>"select data from table where rownum=1 order by date desc" -> returns a bad
>value, not the last
>
> thanks
>
>
select data from t where date = ( select max(date) from t);
or in 8i and up, you can also use:
select data
from ( select data from t order by date DESC )
where rownum = 1;
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Sun Jan 20 2002 - 09:43:59 CST