Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Increment data while select Expand / Collapse
Author
Message
Posted Wednesday, November 10, 2010 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 24, 2012 12:31 PM
Points: 1, Visits: 11
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?

Post #1018902
Posted Wednesday, November 10, 2010 1:09 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I'm not clear on what you want. Can you show an example?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1018915
Posted Wednesday, November 10, 2010 1:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 6,158, Visits: 7,223
I think you're looking for this (Please note how I adjusted your build to create the full test script):

DECLARE @MyTable TABLE ( FirstCol VARCHAR(50), SecondCol INT)

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);


Select
FirstCol,
SecondCol,
ROW_NUMBER() OVER ( PARTITION BY SecondCol ORDER BY FirstCol) * 100 AS Incrementor
From
@MyTable
ORDER BY
SecondCol, FirstCol




- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1018917
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse