SQL & PL/SQL

Question
For the most recent 3 years, show the 5 most popular A-level subjects in that year over the whole country (measure popularity as the number of entries for that subject as a percentage of the total number of exam entries).

select  distinct year
  from  (
         select  distinct year,
                 dense_rank() over(order by year desc) drnk
           from  year_col
        )
  where drnk <= 3
  order by year desc

This lists out the 3 latest years from the year_col column.

2006
2005
2004

will fetch the most recent 3 years but this needs to be implemented in the query to fetch the 5 most popular A-level subjects which i am judging are the top 5 subjects taken by the maximum no of students.

Here 2 conditions are being checked
1.3 recent years which we get from the query above
2.5 most popular subjects based on the max no of students taking the subjects.

I am facing problem incorporating the condition in the query
The Query is wrong but the final query needs to be in the lines of this

It needs to calculate the recent 3 years and for those years needs to display the 5 most popular subjects being chosen most by students.

select     distinct count(s.upn)
,    count(sb.subject)*100/(select count(sid) from subject) as sub
,    c.sn
,    sb.subject
,    y.year
,       dense_rank() over(order by year desc) drnk
from     student_info s
,    student_group sg
,    subject sb
,    year_col y
,    country c
,    grades_list g
where     s.school=y.id
and    drnk <= 3
and    c.id=y.id
and    s.upn=sg.upn
and    sg.gid=y.gid
and     y.sid=sb.sid
and    y.exam_lev=g.exam_level
and    g.exam_level='a'
and    g.grade=sg.grade
group by c.sn,sb.subject
order by year desc;

Expected results

Popular_Subject  Year 
Maths                2006
Science              2005
English               2004

Additional Information

select * from student_info;

UPN                    SCHOOL
-------------------- ----------
1000			    1
...
916                       2
917                       0

9000 rows selected.

select * from grades_list;

EXAM_LEVEL GRADE  VALUE
---------- ---------- ----------
g          E                   3
g          G                   1
g          U                   0
a          B                 100
a          C                  80
a          D                  60
a          F                  20
g          X                   0
a          E                  40
g          C                   5
a          A                 120

EXAM_LEVEL GRADE  VALUE
---------- ---------- ----------
g          A                   7
g          B                   6
a          U                   0
a          X                   0
g          F                   2
g          A*                  8
g          D                   4

QL> select * from year_col;

      YEAR        SID        GID EXAM_LEV   ID
-------------------------------------------------- ----------
      1995        828      15112   g          1

      1995        828      15113   g         2

      1995        828      15114   a         0
....

select * from subject

SID SUBJECT                                            COMPULSORY
---------- -------------------------------------------------- ----------
       835 German                                            0
       836 Drama                                              0
       857 PureMaths                                        0
       869 TechnologyFood                               -1
...

select * from student_group;
UPN         GID	GRADE
--------------------------------------------------
6387       15209 A

6387       15218 B

6387       15233 A*

95854 rows selected.

select * from country;
SN                                                         ID
-------------------------------------------------- ----------
9365026                                                 0
9364529                                                 1
9367985                                                 2

Edited by: Trooper on Jan 4, 2009 2:14 PM