Query help

From: Shweta <google_n_at_hotmail.com>
Date: 28 Oct 2002 17:01:05 -0800
Message-ID: <7cca2fbf.0210281701.11ef03b9@posting.google.com>

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 Received on Mon Oct 28 2002 - 19:01:05 CST