Home » SQL & PL/SQL » SQL & PL/SQL » how to get max semester and year (merged 3)
how to get max semester and year (merged 3) [message #382904] Mon, 26 January 2009 04:10 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Hi.

I want to display the last semester attended by the student.

like the following two records

  970083 2.0297794      2003         1
 2005100 3.0990678      2008         3

from the query as given below. But I am confused how to acquire 
the required results.

Please Help



SQL> desc it
 Name                            Null?    Type
 ------------------------------- -------- ----
 REGNO                           NOT NULL NUMBER(8)
 SY                              NOT NULL NUMBER(4)
 SC                              NOT NULL NUMBER(1)
 SCH                                      NUMBER
 SGP                                      NUMBER
 SGPA                                     NUMBER
 CCH                                      NUMBER
 CGP                                      NUMBER
 CGPA                                     NUMBER

SQL> ;
  1  select regno,cgpa,sy,sc
  2  from it
  3  where regno IN(2005100,970083)
  4* order by regno,sy,sc
SQL> /

    REGNO      CGPA        SY        SC
--------- --------- --------- ---------
   970083 1.0011765      1997         3
   970083 2.4052174      1998         1
   970083 2.1666667      1998         2
   970083 1.7916667      1998         3
   970083  1.622623      1999         1
   970083 1.6880328      1999         2
   970083     1.745      1999         3
   970083  1.804878      2000         1
   970083 1.8587059      2000         2
   970083 1.7492079      2000         3
   970083 1.7047368      2001         1
   970083  1.715814      2001         3
   970083 1.8397778      2002         1
   970083 1.8546667      2002         2
   970083 1.9414706      2002         3
   970083 2.0297794      2003         1
  2005100 3.3529412      2005         3
  2005100 3.3438235      2006         1
  2005100 3.2490196      2006         3
  2005100 3.1376471      2007         1
  2005100 3.1515116      2007         3
  2005100 3.0867308      2008         1
  2005100 3.0990678      2008         3

23 rows selected.

SQL> 




Re: how to get max semester and year [message #382907 is a reply to message #382904] Mon, 26 January 2009 04:16 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
There are atleast couple of ways to approach this problem.

a) Use analytic functions like Rank, Dense_rank or Row_Number and filter it in the outer query.

b) Use aggregate function and supplement it with keep dense_rank logic.

Search for these in this forum. You will find loads of examples.

Thanks for formatting the post. However I notice the same topic has been posted three times. I am not sure whether it is a technical glitch.

From next time if you could post the sample data preferabbly in a with statement it will be lot more easier for us to work with.

Regards

Raj
Re: how to get max semester and year [message #382917 is a reply to message #382907] Mon, 26 January 2009 05:26 Go to previous messageGo to next message
imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad
Member



select a.reg,a.yr,test11.sc
from test11,(select max(sy) yr,regno reg from test11 group by regno) a
where test11.REGNO=a.reg and test11.SY=a.yr



here is the code and i hope that next time, posting your problem would be your last option as this isn't a difficult task unless you spend some time on it.
Re: how to get max semester and year [message #382999 is a reply to message #382917] Mon, 26 January 2009 23:38 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Hi

Thanks a lot. Actually the query still returns multiple rows for the student, which is not the required result.

I actually need the last semester attended by the student.

here is the result with multiple rows.

for example reg 2003903 is retuned with three rows. I just need the last row.
    select a.reg,a.yr,it.sc
    from it,(select max(sy) yr,regno reg from it where regno like '20039%' group by regno) a
    where it.REGNO=a.reg and it.SY=a.yr

SQL> /

      REG        YR        SC
--------- --------- ---------
  2003901      2005         1
  2003902      2005         1
  2003903      2005         1
  2003903      2005         2
  2003903      2005         3
  2003904      2005         1
  2003905      2007         1
  2003905      2007         2
  2003905      2007         3
  2003906      2005         1
  2003907      2005         1
  2003908      2004         1
  2003908      2004         2
  2003908      2004         3
  2003909      2008         1
  2003910      2008         1
  2003911      2007         1
  2003911      2007         2
  2003911      2007         3
  2003912      2008         1
  2003912      2008         3
  2003913      2008         1
  2003914      2008         1
  2003914      2008         2
  2003914      2008         3

25 rows selected.

Please suggest

[Updated on: Tue, 27 January 2009 00:38] by Moderator

Report message to a moderator

Re: how to get max semester and year (merged 3) [message #383009 is a reply to message #382904] Mon, 26 January 2009 23:49 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
The solution is in the same post,

look what S.Rajaram has written.

a) Use analytic functions like Rank, Dense_rank or Row_Number and filter it in the outer query.

b) Use aggregate function and supplement it with keep dense_rank logic.

Thanks
Trivendra

[Updated on: Tue, 27 January 2009 00:38] by Moderator

Report message to a moderator

Re: how to get max semester and year (merged 3) [message #383021 is a reply to message #383009] Tue, 27 January 2009 00:10 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
OK... thank you
I am trying..if not succeeded I will come here again..to tease you people

good luck to me and all
Re: how to get max semester and year (merged 3) [message #383312 is a reply to message #383021] Wed, 28 January 2009 04:01 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I have managed to find semester number. But I am still unable to filter the result
and get the maximum semester alongwith regno,sc,sy,cgpa cols.

please help



SQL> ;
  1  select regno,sc,sy,cgpa,
  2  rank () over(partition by regno order by sy,sc) as sem_val
  3   from it
  4* where regno like '20053%'
SQL> /

    REGNO        SC        SY      CGPA   SEM_VAL
--------- --------- --------- --------- ---------
  2005300         3      2005 2.2358824         1
  2005300         1      2006 2.6351613         2
  2005300         3      2006 3.0497561         3
  2005300         1      2007 2.9390741         4
  2005300         3      2007 2.9027941         5
  2005300         1      2008 2.7498765         6
  2005300         3      2008 2.5761053         7
  2005301         3      2005 2.5894118         1
  2005301         1      2006 2.6085294         2
  2005301         3      2006 2.7487755         3
  2005301         1      2007 2.6565079         4
  2005301         2      2007 2.6428986         5
  2005301         3      2007 2.5727273         6
  2005301         1      2008 2.5451485         7
  2005301         3      2008 2.5645299         8

15 rows selected.

SQL> 



Thanks

Riaz
Re: how to get max semester and year (merged 3) [message #383483 is a reply to message #383312] Thu, 29 January 2009 00:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
If you order by sy desc, sc desc, then the last semester would have sem_val = 1, so you could use that whole query as an inline view (sub-query in the from clause) and select only those rows where sem_val = 1 from an outer query.
Re: how to get max semester and year (merged 3) [message #383523 is a reply to message #383483] Thu, 29 January 2009 01:22 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thank you...

It worked and saved a lot of my time

Riaz
Previous Topic: QUERY
Next Topic: Test Data printing (merged)
Goto Forum:
  


Current Time: Sat Dec 10 14:37:09 CST 2016

Total time taken to generate the page: 0.11563 seconds