July 15, 2015 at 1:13 pm
OK, I'm having a brain freeze today because I SHOULD know this but I can't even think of the solution today. I have the following sets of records:
ColA ColB
----- -----
21 A
22 A
23 A
24 B
25 B
26 D
What I want is to be able to identify a set sequence (1,2,3) based upon ColB such that I'd get the following result:
ColA ColB ColC
----- ----- -----
21 A 1
22 A 1
23 A 1
24 B 2
25 B 2
26 D 3
I know that I should be able to get it using ROW_NUMBER() OVER (PARTITION BY ColB ORDER BY ColA), but instead of getting the sequence (1,1,1,2,2,3) I get (1,2,3,1,2,1). Using DENSE_RANK gave me the same results.
Thanks in advance.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
July 15, 2015 at 1:29 pm
Yeah, I can't think of a really clean way to do it now either, other than brute force:
SELECT table_name.*, derived2.row_num
FROM table_name
INNER JOIN (
SELECT colb,ROW_NUMBER() OVER(ORDER BY colb) AS row_num
FROM (
SELECT DISTINCT colb
FROM table_name
) AS derived1
) AS derived2 ON
derived2.colb = table_name.colb
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 15, 2015 at 1:32 pm
If I understand what you want, you don't need to partition the result, since you want the number sequence to persist across all the values, based solely on ColB.
At least for the sample data this works:
DENSE_RANK() OVER (ORDER BY ColB)
Having said that, I'm not sure if/how ColA is supposed to affect the desired results. Do you only want the same group number if rows with the same ColB value have consecutive values in ColA?
Cheers!
July 15, 2015 at 1:37 pm
Jacob Wilkins (7/15/2015)
If I understand what you want, you don't need to partition the result, since you want the number sequence to persist across all the values, based solely on ColB.At least for the sample data this works:
DENSE_RANK() OVER (ORDER BY ColB)Having said that, I'm not sure if/how ColA is supposed to affect the desired results. Do you only want the same group number if rows with the same ColB value have consecutive values in ColA?
Cheers!
Oh for crying out loud! I can't believe that I overcomplicated it! :w00t:
Actually, the value in ColA is irrelevant for grouping purposes. I just put those in there to represent the values that I wanted grouped and ColB is the grouper.
Thanks a bunch!!
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
July 15, 2015 at 1:44 pm
I'm glad I could help!
Oh for crying out loud! I can't believe that I overcomplicated it!
We've all been there. 🙂
Cheers!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply