SQL & PL/SQL

Hi,
I have one doubt

My query is

Subscription table
----------------------------

subscription_nr person_nr sub_begin_date sub end_date promotion_nr
101 1 10-sep-2004 10-nov-2004 3
108 1 10-nov-2004 10-jan-2005 6
110 1 10-jan-2005 10-mar-2005
121 2 10-sep-2004 25-nov-2004
128 2 25-nov-2004 10-jan-2005 8
130 2 10-jan-2005 10-mar-2005


i want the report like list of person whose program ends on november month 2004 (sub_end_date) and who has renevewed for next program with promotion nr as 6 (promotion_nr) but when i try for using dense rank i the query is retruning only 1 row

the query is

SELECT * FROM (
SELECT person_nr,subscription_nr ,dense_rank() over (ORDER BY subscription_nr ASC) jr FROM subscription WHERE promotion_nr=6 AND
TRUNC(subscription_begin_date) BETWEEN TRUNC(TO_DATE('10/01/2004','mm/dd/yyyy')) AND TRUNC(TO_DATE('10/31/2004','mm/dd/yyyy')) AND
person_nr IN (SELECT s.person_nr FROM subscription s, job_queue q WHERE s.person_nr = q.person_nr AND
job_queue_type_nr = 4 AND status_code = 'FNSH' AND
TRUNC(subscription_end_date) BETWEEN TRUNC(TO_DATE('10/01/2004','mm/dd/yyyy')) AND TRUNC(TO_DATE('10/31/2004','mm/dd/yyyy')) AND subscription_nr IN
(SELECT MIN(subscription_nr) FROM subscription s1 WHERE s.person_nr = s1.person_nr AND subscription_end_date IS NOT NULL))) WHERE jr=2



can anyone pls help me
thanks




Sathish