CODEWITHSUNDEEP
sqlsql-server-2016
คมน์ณัฐ วรรณธนศิลป์
คมน์ณัฐ วรรณธนศิลป์

Reputation: 13

SQL Server Add row number each group

I working on a query for SQL Server 2016. I have order by serial_no and group by pay_type and I would like to add row number same example below

row_no | pay_type | serial_no
   1   |    A     | 4000118445
   2   |    A     | 4000118458
   3   |    A     | 4000118461
   4   |    A     | 4000118473
   5   |    A     | 4000118486
   1   |    B     | 4000118499
   2   |    B     | 4000118506
   3   |    B     | 4000118519
   4   |    B     | 4000118521
   1   |    A     | 4000118534
   2   |    A     | 4000118547
   3   |    A     | 4000118550
   1   |    B     | 4000118562
   2   |    B     | 4000118565
   3   |    B     | 4000118570
   4   |    B     | 4000118572

Help me please..

Upvotes: 1

Views: 3919

Answers (4)

amads
amads

Reputation: 1

This query will generate a result set where each pay_type grouping has its own set of row numbers, incrementing based on the order of serial_no. The row numbers will restart for each new pay_type.

SELECT 
    ROW_NUMBER() OVER (PARTITION BY pay_type ORDER BY serial_no) AS row_no,
    pay_type,
    serial_no
FROM 
    your_table_name;

Upvotes: 0

eshirvana
eshirvana

Reputation: 24593

add this to your select list

 ROW_NUMBER() OVER ( ORDER BY (SELECT 1) )

since you already sorting by your stuff, so you don't need to sorting in your windowing function so consuming less CPU,

Upvotes: 0

user3158212
user3158212

Reputation: 525

SELECT
    ROW_NUMBER() OVER(PARTITION BY paytype ORDER BY serial_no) as row_no,
    paytype, serial_no
FROM table
ORDER BY serial_no

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269963

You can assign groups to adjacent pay types that are the same and then use row_number(). For this purpose, the difference of row numbers is a good way to determine the groups:

select row_number() over (partition by pay_type, seqnum - seqnum_2 order by serial_no) as row_no,
       t.*
from (select t.*,
             row_number() over (order by serial_no) as seqnum,
             row_number() over (partition by pay_type order by serial_no) as seqnum_2
      from t
     ) t;

This type of problem is one example of a gaps-and-islands problem. Why does the difference of row numbers work? I find that the simplest way to understand is to look at the results of the subquery.

Here is a db<>fiddle.

Upvotes: 0

Related Questions