November 10, 2010 at 12:59 pm
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?
November 10, 2010 at 1:09 pm
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
November 10, 2010 at 1:14 pm
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
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply