Re: Query help

Shweta wrote:

> Hi all,
>
> My data looks like this
> Table YearData
>
> Year RevisedNumber ID
> 1998 0 111
> 1999 0 112
> 1998 1 113
> 1999 0 114
>
> Ok, My aim is to get the ID for the maximum revisedNumber for an year.
>
> Say, the result has to be
> If the input is 1998, the record with the max revisedNumber, ie 1 has
> to be the output. So it is 111
> In case of 1999, since the max revNumber is 0, both records have to be
> outputted
>
> Whats the best query in this case ???
>
> I tried writing like this , but I want to improve the query..
>
> SELECT REVISEDNUMBER,YEAR,ID FROM YEARDATA
> WHERE YEAR= ? and REVISEDNUMBER = (select max(REVISEDNUMBER) from
> YEARDATA WHERE YEAR = ?)
>
> Please help
> Thanks

Two options come to mind immediately.

SELECT DISTINCT
WHERE rownum = 1

Daniel Morgan Received on Tue Oct 29 2002 - 10:47:54 CST