Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Resetting Row number according to record data change

I have got the set of data as follow

name  date  
x     2014-01-01
x     2014-01-02
y     2014-01-03
x     2014-01-04

and I'm trying to get this result

name  date           row_num
x     2014-01-01      1
x     2014-01-02      2
y     2014-01-03      1
x     2014-01-04      1

I have tried to run this query

select name,
    date,
    row_number () over (partition by name order by date) as row_num
from myTBL

but unfortunately I get this result

name  date           row_num
x     2014-01-01      1
x     2014-01-02      2
y     2014-01-03      1
x     2014-01-04      3

Please help.

like image 373
erezlale Avatar asked Dec 28 '14 21:12

erezlale


People also ask

Does Rownum numbers record in a result set?

ROWNUM numbers the records in a result set. The first record that meets the WHERE clause criteria in a SELECT statement is given a row number of 1, and every subsequent record meeting that same criteria increases the row number.

What is ROW_NUMBER () in SQL?

ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5). ROW_NUMBER is a temporary value calculated when the query is run. To persist numbers in a table, see IDENTITY Property and SEQUENCE.

What is ROW_NUMBER () and partition by in SQL Server?

PARTITION BYIt is an optional clause in the ROW_NUMBER function. It is a clause that divides the result set into partitions (groups of rows). The ROW_NUMBER() method is then applied to each partition, which assigns a separate rank number to each partition.

Can we use Rownum in WHERE clause?

One can use row_number function in where clause in SQL server by Common Table Expression (CTE) as shown in the image below.


1 Answers

You need to identify the groups of names that occur together. You can do this with a difference of row numbers. Then, use the grp for partitioning the row_number():

select name, date,
       row_number() over (partition by name, grp order by date) as row_num
from (select t.*,
             (row_number() over (order by date) -
              row_number() over (partition by name order by date)
             ) as grp
      from myTBL t
     ) t

For your sample data:

name  date         1st row_number   2nd      Grp
x     2014-01-01         1           1        0
x     2014-01-02         2           2        0
y     2014-01-03         3           1        2
x     2014-01-04         4           3        1

This should give you an idea of how it works.

like image 172
Gordon Linoff Avatar answered Sep 19 '22 19:09

Gordon Linoff