Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JMOE
Starting Member
4 Posts |
 Posted - 2011-06-27 : 17:07:37
|
| Hello all, Looking for the correct way to add a unique or first instance column for my query.would like to be able to add a frist count as 1 on the frist instance and then 0 for every instance after that.BeforeType STATE DATEAPPLE AZ 6/25/2011APPLE AZ 6/26/2011APPLE CA 6/27/2011GRAPE AZ 6/28/2011GRAPE AZ 6/25/2011NUT CA 6/26/2011NUT CA 6/27/2011NUT NM 6/28/2011afterType STATE DATE Frist instanceAPPLE AZ 6/25/2011 1APPLE AZ 6/26/2011 0APPLE CA 6/27/2011 0GRAPE AZ 6/28/2011 1GRAPE AZ 6/25/2011 0NUT CA 6/26/2011 1NUT CA 6/27/2011 0NUT NM 6/28/2011 0Thanks Joe |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-06-27 : 17:34:58
|
;WITH cteSource AS (SELECT Type, State, Date, ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Date) AS SeqID FROM Table1)SELECT Type, State, Date, CASE WHEN SeqID = 1 THEN 1 ELSE 0 END AS [First instance] FROM cteSource
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
|
|
|
Unique count