SQL Query Problem

[smartsunny]

Hello,

I am having hard time to solve this query.

I have the data such as following.

SESSION SCHOOL CLASS SESSIONDATE
1 1 1 01-JAN-05
2 1 1 02-JAN-05
3 1 2 03-JAN-05
4 1 2 04-JAN-04

I would like to retrieve school and class of the most recent session.

Output would be something like

SESSION
2
4

What is the best way to solve the above query.
Any help appreciated.

Thanks in advance,

 

[tdatgod]

Hi,
I don't understand how session 4 could be the most recent since it was a year ago. Maybe that was just a typo in your post.

I had to put a MY in front of all the Column names because Session and Class are reserved words.

I used the Title clause to preserve the output Column names.

create table mytable  
( MySession integer title 'Session', 
  MySchool integer title 'School',
  MyClass integer title 'Class',
  MySessiondate  date title 'SessionDate'
)
primary index(MySession);


insert into mytable
values(1,1,1,'2005/01/01');

insert into mytable
values(2,1,1,'2005/01/02');

insert into mytable
values(3,1,2,'2005/01/03');

insert into mytable
values(4,1,2,'2004/01/04');


sel  a.mysession,a.myclass 
  from mytable a ,
     ( sel max(mysessiondate) as maxsessiondate from mytable ) b
      where a.mysessiondate = b.maxsessiondate; 


 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.

    Session        Class
-----------  -----------
          3            2

 

[BillDHS]

You could use a derived table to find the Max date on a school - class combination, and then go back and get the session

Select session
from tabla,
(select school || class, MAX (SessionDate)
from tabla
group by 1) tablb(sescls, maxdate)
where tabla.school || tabla.class = tablb.sescls
and tabla.SessionDate = tablb.maxdate ;

 

[dnoeth]

No need for nested queries, just use OLAP SQL:

select *
from tab
qualify
rank() over (partition by school, class
order by sessiondate desc) = 1


Dieter