Re: Help needed in SQL Query

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. Received on Tue Aug 19 2003 - 07:12:58 CEST