OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Please help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please help [message #3955]

Tue, 29 October 2002 08:01

Maya
Messages: 7
Registered: May 2002

Junior Member

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

Report message to a moderator

Re: Please help [message #3960 is a reply to message #3955]

Tue, 29 October 2002 10:28

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Actually, for 1998, the ID would be 113. Your query is good - I would recommend turning it into a correlated query so the year only has to be specified in one place:

sql>select * from yeardata order by year, revisednumber;
 
     YEAR REVISEDNUMBER        ID
--------- ------------- ---------
     1998             0       111
     1998             1       113
     1999             0       112
     1999             0       114
 
4 rows selected.
 
sql>var v_year number
sql>exec :v_year := 1998
 
PL/SQL procedure successfully completed.
 
sql>select revisednumber, year, id
  2    from yeardata y1
  3   where year = :v_year
  4     and revisednumber = (select max(revisednumber)
  5                            from yeardata y2
  6                           where y2.year = y1.year);
 
REVISEDNUMBER      YEAR        ID
------------- --------- ---------
            1      1998       113
 
1 row selected.
 
sql>exec :v_year := 1999
 
PL/SQL procedure successfully completed.
 
sql>select revisednumber, year, id
  2    from yeardata y1
  3   where year = :v_year
  4     and revisednumber = (select max(revisednumber)
  5                            from yeardata y2
  6                           where y2.year = y1.year);
 
REVISEDNUMBER      YEAR        ID
------------- --------- ---------
            0      1999       112
            0      1999       114
 
2 rows selected.

Report message to a moderator

Previous Topic:	repeating a queary several times
Next Topic:	attention todd barry or andrew

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 05 05:39:03 CDT 2024