Min Identity for Grouped Records 1

[Khanson2]

I have basic data like this.
and I want to add the column you see out the far right.
Identity ID Contactid Desired Column
1 A 123 1
2 A 124 1
3 A 125 1
4 B 200 4
5 B 201 4
6 C 354 6
7 C 368 6
8 C 370 6
9 C 395 6
10 D 400 10

All the desired column is doing is getting the min identity valuewhere the ID columns starts.
Any ideas?

Thanks,

 

[wbodger]

Is this something that you want to happen automatically, something you just need to run once or you will run regularly?

wb

 

[Khanson2]

I have all the data to this point in a temp table and I need to add this column to the data set, so I can then get the ordinal # for these events to better analyze it.
The data will change daily, so I just need the method of creating this information in sql when I run the scripts.

Thanks,

 

[wbodger]

well, something like this will give you the value you want to insert into the column

select a.id, MIN(a.autoid) 
from [dbTesting].[dbo].[TestTable] a
	join [dbTesting].[dbo].[TestTable] b on a.id=b.id
group by a.id

wb

 

[markros]

select *, rank() over (order by ID) as [Desired Column]
from yourtable -- SQL Server 2005 and up

PluralSight Learning Library

 

[Khanson2]

Thanks wbodger. I used this logic and then joined back to the main dataset and all worked.

 

[markros]

If you're using SQL Server 2005 and up then use built-in RANK() window function.

PluralSight Learning Library

 

[Khanson2]

Yes that's right Markros, sorry, when I first used yours it didn't look right, but after trying again I get desired results..

Thanks!

 

[wbodger]

I'm new to the post SQL2K versions, how exactly would you use the rank() function? I cannot get it to act nicely.

wb

 

[wbodger]

Oh. Nevermind. I was trying to do too much. That's pretty cool.

wb

 

[wbodger]

Now, why does it work? In my test table I names the identity column AutoID, which is what shows up in the [Desired Col] field, but there is no command for Rank to use this, is there? What does Rank() assume/default to?

wb

 

[markros]

In the OP sample the ID column has the same values first 3 rows have A in ID, next 2 B, etc.

So

     Rank Row_number Dense_Rank
 
A    1     1           1
A    1     2           1 
A    1     3           1
B    4     4            2

PluralSight Learning Library