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 |
|
smorty44
Yak Posting Veteran
93 Posts |
 Posted - 2007-11-05 : 12:16:41
|
| I'm looking for a query that will look at an Id field and if it occurs more than once then returns the count of the times it occurs. For Example,ID Code GetSequence4 239 14 241 24 3243 3 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-11-05 : 12:21:24
|
| select id, count(*)from tblgroup by idor looks like you might wantselect id, code, GetSequence = (select count(*) from tbl t2 where t.id = t2.id and t2.code <= t.code)from tbl t==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-05 : 13:03:07
|
Or ROW_NUMBER() function if using SQL Server 2005.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-06 : 00:52:56
|
| or if you want to show them in reports, group it by id and reset counter in each groupMadhivananFailing to plan is Planning to fail |
|
|
|
arorarahul.0688
Posting Yak Master
125 Posts |
Posted - 2007-11-06 : 08:32:15
|
| try thisselect count(id) from testing group by idRahul Arora MCA 07 BatchNCCE Israna, PanipatHRY, INDIA######################IMPOSSIBLE = I+M+POSSIBLE |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-11-13 : 16:43:37
|
quote:
Originally posted by arorarahul.0688
try thisselect count(id) from testing group by idRahul Arora MCA 07 BatchNCCE Israna, PanipatHRY, INDIA######################IMPOSSIBLE = I+M+POSSIBLE
I think you mean:select id, count(id) from testing group by id |
|
|
|
|
|
|
Sequence Counter