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,

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

wb

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,

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


wb

PluralSight Learning Library

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

 

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

PluralSight Learning Library

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!

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

wb

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

wb

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

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

So

PluralSight Learning Library