Re: Help needed in SQL Query

Originally posted by Surendra

> surendramuthye_at_yahoo.com (Surendra) wrote in message
> news:<742a5eb5.0308180922.fbc759c_at_posting.google.com>...
 

> > LKBrwn_DBA <member30625_at_dbforums.com> wrote in message
> news:<3231459.1060727002_at_dbforums.com>...
 

> > > Try this (for Oracle 8i+):
 

> > >
 

> > > Select Grp_Code, End_Date,
 

> > > Rank() Over(Partition By Grp_Code Order By End_Date) As
> Sq
 

> > > From The_Table Group By Grp_Code, End_Date;
 

> >
 

> > Thank you very much. This definitely gives me the result that I
> was looking for.
 

>
 

>
 

> This query works well to give the output as desired.
 

>
 

> Here is the query that I used
 

>
 

> Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank()
 

> Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by
 

> End_Date)
 

> As Sq From The_Table
 

> Group by substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date
 

> where substr(to_date(End_Date,"DD-MON-YYYY"),4)= "Jun-2003"
 

>
 

> This gives me (exactly as I wanted)
 

> Jun-2003 02-06-2003 1
 

> Jun-2003 16-06-2003 2
 

> Jun-2003 30-06-2003 3
 

>
 

> My next question is how do I extract the Rank() from this result. I
 

> cannot add a WHERE or HAVING clause specifying the input End_Date as
 

> this will impact the sequencing. So for example, if the input Date is
 

> 16-06-2003, I should get 2 as the result. Based on 2, I have to do
 

> further processing.
 

>
 

> Thanks in advance for the input.

Make the query into an inline view:

select *

from

(

Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank()

Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by

End_Date)

As Sq From The_Table

Group by substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date

where substr(to_date(End_Date,"DD-MON-YYYY"),4)= "Jun-2003"

)

where End_Date = TO_DATE('16-06-2003','DD-MM-YYYY');

But instead of substr(to_date(End_Date,"DD-MON-YYYY"),4) use trunc(End_Date,'MM') which will return the 1st of the month.

--
Posted via http://dbforums.com