SQL - Help with Analytic function

[rshivagami]

This is the data I have :

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 :

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 ?

[hrishy]

Hi

You might want to try

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

[rshivagami]

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 :

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)

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

[hrishy]

Hi

It would help if you could post
create table
and insert statements with sample data for me to work on

regards
Hrishy

[ebrian]

The following is another option:

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