SQL: without a cursor, how to select records making a unique integer id (identity like) for dups?
- by Dr. Zim
If you have the select statement below where the PK is the primary key:
select distinct dbo.DateAsInt( dateEntered) * 100 as PK,
recordDescription as Data
from MyTable
and the output is something like this (the first numbers are spaced for clarity):
PK Data
2010 01 01 00 New Years Day
2010 01 01 00 Make Resolutions
2010 01 01 00 Return Gifts
2010 02 14 00 Valentines day
2010 02 14 00 Buy flowers
and you want to output something like this:
PK Data
2010 01 01 01 New Years Day
2010 01 01 02 Make Resolutions
2010 01 01 03 Return Gifts
2010 02 14 01 Valentines day
2010 02 14 02 Buy flowers
Is it possible to make the "00" in the PK have an "identity" number effect within a single select? Otherwise, how could you increment the number by 1 for each found activity for that date?
I am already thinking as I type to try something like Sum(case when ?? then 1 end) with a group by.
