SQL - Help with Analytic function

Printable View

03-03-2009, 11:46 PM
rshivagami

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 ?
03-04-2009, 04:20 AM
hrishy

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
03-04-2009, 11:20 AM
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 :

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
03-06-2009, 01:39 AM
hrishy

Hi

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

regards
Hrishy
03-06-2009, 02:37 AM
ebrian

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