Ranking Over Row_Number in SQL

Question

I am posting a sample data below.

What I have is a row number which generated a number based on Date and Name columns (achieved using ROW_NUMBER function). What I need now, is another derived column called Group_Num which creates a number for each group (3 in this case). Can this be achieved considering the fact that my Name column repeats but the Date column value changes?

Thanks in advance.

Please remove the picture and add sample data as DDL + DML, and also show the query you currently have. — Zohar Peled, Feb 07 '17 at 06:49
http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Feb 07 '17 at 06:56

Mr. Bhosale · Accepted Answer · 2017-02-08T08:57:13.330

Check This. We can achive this using Row_number() ,lag() and SUM() .

        select 
        Date,
        Name,
        Row_number()over( partition by Group_Num order by ROwiD ) Row_Num,
        Group_Num
        from 
        (
            SELECT * ,  
            SUM(R) OVER(ORDER BY RowID) Group_Num
            FROM
            (
                select *,
                Case When 
                lag(name ) OVER (ORDER BY RowID ) = name
                then 0  else 1 end  as R 
                from 
                    (
                        select DATE,NAME,
                        row_number() OVER (    ORDER BY  (select 1)) AS 'RowID'
                        from #TableName
                    )A
            )B
         )C
        order by ROwiD

OutPut :

Thanks for your answer. But if I use this, it will return the Group_Num as "1" for the last 3 rows. I need it as 3 (even though the name is same). — Triumph Spitfire, Feb 07 '17 at 22:42

score 2 · Answer 2 · answered Feb 07 '17 at 07:00

2

You can use DENSE_RANK:

SELECT Date
    , Name
    , ROW_NUMBER() OVER(Partition By Name Order By Name, Date) as Row_Num
    , DENSE_RANK() Over(order by Name) as Group_Num 
FROM #Table

answered Feb 07 '17 at 07:00

Kim

771
6
23

Thanks for your answer. But if I use this, it will return the Group_Num as "1" for the last 3 rows. I need it as 3 (even though the name is same). – Triumph Spitfire Feb 08 '17 at 00:39

Ranking Over Row_Number in SQL

2 Answers2