Increment data while select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Increment data while select

Author

Topic

Waverider
Starting Member

3 Posts

Posted - 2010-11-10 : 14:50:50

Hello,

I am having an issue with a select statement I am trying to run. I need to select some data from a table, which has multiple records for one customer ID. I need to add a static column in my select, and then incrememnt that column for each record per customer ID.

If I create this Table:
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Waverider',1);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Psalms',2);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('WRV',3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('18th Street',3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Soundside',3);
GO

Then try this Select:
Select
FirstCol,
SecondCol,
'100' as ThirdCol
From MyTable

I want to increment the ThirdCol by 100 where the SecondCol is the same?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-10 : 15:00:53

Could you show us expected output using the sample data you provided?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-10 : 15:02:48

If you have sql server 2005 or later you can use:


Select  FirstCol,
        SecondCol,
        100 * row_number() over (partition by secondCol order by firstCol) as ThirdCol
From    MyTable

OUTPUT:
FirstCol             SecondCol   ThirdCol
-------------------- ----------- --------------------
Waverider            1           100
Psalms               2           100
18th Street          3           100
Soundside            3           200
WRV                  3           300

Be One with the Optimizer
TG

Waverider
Starting Member

3 Posts

Posted - 2010-11-10 : 15:34:47

tkizer - TG posted example of waht I am looking for.

TG - that is what I am looking for except I need it to start with 200?? AND, I forgot to add I am doing a Union on a differnt select as well.

I know this is probaably not best way to run this Query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-10 : 15:41:12

100 + 100 * row_number()...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Waverider
Starting Member

3 Posts

Posted - 2010-11-12 : 09:27:02

Thank you Tkizer and TG. This worked perfectly.

Any suggestions of books, training programs, blogs or anything I can use to master issues like these?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources