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 |
|
jmoe007
Starting Member
1 Post |
 Posted - 2011-06-28 : 13:26:03
|
| Hello all, struggling to figure out how to do a Distinct first instance, if that’s the correct way to state this.Trying to add a (1 or o) column that will add 1 to the first instance of the type and 0 for any other within my first instance column as way to create a unique count for another report.TYPE STATE DATEAPPLE AZ 5/1/2020APPLE MN 5/20/2010APPLE NY 5/20/2010GRAPE NY 5/1/2020GRAPE CA 5/20/2010ORANGE CA 5/1/2020ORANGE NY 5/20/2010ORANGE AZ 5/20/2010So the end result would be TYPE STATE DATE first countAPPLE AZ 5/1/2020 1APPLE MN 5/20/2010 0APPLE NY 5/20/2010 0GRAPE NY 5/1/2020 1GRAPE CA 5/20/2010 0ORANGE CA 5/1/2020 1ORANGE NY 5/20/2010 0ORANGE AZ 5/20/2010 0Thanks Joe |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-28 : 13:38:05
|
| select type, state, firstcount = case when seq = 1 then 1 else 0 end)from(select *, seq = row_number() over (partition by type order by date)from tbl) aOr you could just useselect *, seq = row_number() over (partition by type order by date)from tbland use the sequence for your processing.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Distinct first count