SQL increment a value based on different column

Question

Table 1 looks like this:

Name             Date         Hours    HoursType   

Doe, John        8/7/18       22        A
Doe, John        8/7/18       10        B
Doe, John        8/7/18       5         C
Doe, Jane        8/7/18       15        A
Doe, Jane        8/7/18       40        B

I'd like a Select that adds an incrementing integer as an additional column ideally it will produce results that looks like this:

Doe, John        8/7/18       22        A     1
Doe, John        8/7/18       10        B     2
Doe, John        8/7/18       5         C     3
Doe, Jane        8/7/18       15        A     1
Doe, Jane        8/7/18       40        B     2

The 5th column should start at 1 and increment by 1 for each line of Doe, John. It should start back over at 1 for the next name.

Thanks

Is this SQL Server? If so, have you looked into using ROW_NUMBER? learn.microsoft.com/en-us/sql/t-sql/functions/… — critical_error
– critical_error, Commented Aug 7, 2018 at 20:16

Sean Lange · Accepted Answer · 2018-08-07 20:20:53Z

2

Use ROW_NUMBER. It was made for this. https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017

Not sure how you order the rows since in your sample there is no way to ensure the order but pick something that makes sense with your real data.

select *
    , YourNewColumn = ROW_NUMBER() over(partition by Name order by HoursType)
from YourTable

edited Aug 7, 2018 at 20:20

answered Aug 7, 2018 at 20:16

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

S3S Over a year ago

Only thing is they want the new column to "reset" so probably need rank

Sean Lange Over a year ago

@scsimon - According to the sample output this should be fine. It will restart with each new name. But maybe that isn't what they want.

Gordon Linoff · Accepted Answer · 2018-08-07 20:16:43Z

0

SQL tables are unordered, and there is not an obvious ordering to the values. The answer to your question is row_number(), but with some ordering column or expression. For example:

select t.*,
       row_number() over (partition by name order by (select null)) as hourstype
from t;

Note that the rows will be enumerated, but in an arbitrary order. If there is a column for ordering, use that instead of (select null).

answered Aug 7, 2018 at 20:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

critical_error · Accepted Answer · 2018-08-07 20:24:41Z

You can run this in SSMS:

DECLARE @data TABLE( [Name] VARCHAR(50), [Date] DATETIME, [Hours] INT, [HoursType] VARCHAR(1) );

INSERT INTO @data ( [Name], [Date], [Hours], [HoursType] ) VALUES
( 'Doe, John', '8/7/18', 22, 'A' )
, ( 'Doe, John', '8/7/18', 10, 'B' )
, ( 'Doe, John', '8/7/18', 5, 'C' )
, ( 'Doe, Jane', '8/7/18', 15, 'A' )
, ( 'Doe, Jane', '8/7/18', 40, 'B' );

SELECT
    [Name]
    , [Date]
    , [Hours]
    , [HoursType]
    , ROW_NUMBER() OVER ( PARTITION BY [Name] ORDER BY [Name], [Date], [HoursType] ) AS RowNo
FROM @data
ORDER BY
    [Name], [Date], [HoursType];

Returns

+-----------+-------------------------+-------+-----------+-------+
|   Name    |          Date           | Hours | HoursType | RowNo |
+-----------+-------------------------+-------+-----------+-------+
| Doe, Jane | 2018-08-07 00:00:00.000 |    15 | A         |     1 |
| Doe, Jane | 2018-08-07 00:00:00.000 |    40 | B         |     2 |
| Doe, John | 2018-08-07 00:00:00.000 |    22 | A         |     1 |
| Doe, John | 2018-08-07 00:00:00.000 |    10 | B         |     2 |
| Doe, John | 2018-08-07 00:00:00.000 |     5 | C         |     3 |
+-----------+-------------------------+-------+-----------+-------+

Collectives™ on Stack Overflow

SQL increment a value based on different column

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related