SQL - Help with Analytic function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL - Help with Analytic function

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    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 ?

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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 01:51 AM. Reason: Look at ebrians elegant solution below

  5. #5
    Join Date
    Apr 2006
    Posts
    377
    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
  •  



Click Here to Expand Forum to Full Width