Set-based Aggregates Problem
-
Hi All,
I feel bad asking this cause I have seen it one this site before and have infact done this myself before...
Perhaps the pressure is getting to me he he he
I have one table as follows:
CREATE TABLE TableA
(Value CHAR(1) NOT NULL)
The values in side for this example are say
Value
a
b
b
c
c
d
I am trying to write a query that will return the following results
ID VALUE
1 a
1 b
2 b
1 c
2 c
1 d
OK I can do this using a loop very easily but I would like to avoid using a loop and def not use a cursor.
Any help or tips and ideas to put me in the right direction would be great!
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
select row_number() over(partition by Value order by Value) as ID,
Value
from TableA
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Do you want 1 & 2 to be repeated for each character in the table?
-
Sorry, I misread the question....
What did we do before the days of Ranking functions???
-
HI MarkC ,
that is perfect 10000% correct and so simple.
Sorry I missed that. Didn't realise you could use partition and a order by in a over clause 🙂
thanks again.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply