-
SQL - Help with Analytic function
This is the data I have :
Code:
HIER_LEVEL STANDARD_SOURCE TITLE YEAR_ADOPTED
1 FL Sunshine State Standards 2006
1 FL Sunshine State Standards 2007
1 GA Performance Standards 2004
1 GA Performance Standards 2004
And this is the output I need :
Code:
HIER_LEVEL STANDARD_SOURCE TITLE YEAR_ADOPTED GROUP_ID
1 FL Sunshine State Standards 2006 1
1 FL Sunshine State Standards 2007 2
1 GA Performance Standards 2004 3
1 GA Performance Standards 2004 3
But I am not able to write the correct query. I am using ROW_NUMBER () function. Any other suggestions ?
-
Hi
You might want to try
Code:
select
HIER_LEVEL,
STANDARD_SOURCE,
TITLE,
YEAR_ADOPTED,
row_number() over(partition by TITLE,
YEAR_ADOPTED
order by title,year_adopted) group_id
from table
-
Thanks Hrishy for your response. That is one of the few things I tried, but you are asking to group by Title and year_adopted, so the output looks like this :
Code:
HIER_LEVEL STANDARD_SOURCE TITLE YEAR_ADOPTED GROUP_ID
1 FL Sunshine State Standards 2006 1
1 FL Sunshine State Standards 2007 2
1 GA Performance Standards 2004 1
1 GA Performance Standards 2004 1
This is the closest I could get to, but it is still not correct :
row_number() over(partition by hier_level
order by standard_source, title,year_adopted)
Code:
HIER_LEVEL STANDARD_SOURCE TITLE YEAR_ADOPTED GROUP_ID
1 FL Sunshine State Standards 2006 1
1 FL Sunshine State Standards 2007 2
1 GA Performance Standards 2004 3
1 GA Performance Standards 2004 4
-
Hi
It would help if you could post
create table
and insert statements with sample data for me to work on
regards
Hrishy
Last edited by hrishy; 03-06-2009 at 02:51 AM.
Reason: Look at ebrians elegant solution below
-
The following is another option:
Code:
SQL> select t1.*,
2 dense_rank() over (partition by hier_level
3 order by standard_source, title,year_adopted) group_id
4 from t1;
HIER_LEVEL STANDARD_SOURCE TITLE YEAR_ADOPTED GROUP_ID
---------- --------------- ------------------------- ------------ ----------
1 FL Sunshine State Standards 2006 1
1 FL Sunshine State Standards 2007 2
1 GA Performance Standards 2004 3
1 GA Performance Standards 2004 3
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|