"David Portas" wrote:

> SELECT A.id, A.name, A.city, A.number, COUNT(*)
>  FROM YourTable AS A
>  JOIN YourTable AS B
>   ON A.number = B.number
>    AND A.id >= B.id
>  GROUP BY A.id, A.name, A.city, A.number
>
> --
> David Portas
> SQL Server MVP
> --
>
>
>

"Kutlan" wrote:

> Hi Champs!
> I have a tricky sql problem, and I would apreciate some help from you.
>
> In my SELECT statement my result looks like this:
>
> ID----name----city----number
> 1 ---- lfkjdfl---djdjjd---1212
> 2----- mdsf-- safta----1212
> 3-----jsert----agjyt----1212
> 4-----wqfh---jfgyiu----3434
> 5-----aghj----jqqre----3434
>
>
> But now I want a added column, in my SELECT result, so it would look like:
>
> ID----name----city----number---newColumn
> 1 ---- lfkjdfl---djdjjd---1212----------1
> 2----- mdsf-- safta----1212-----------2
> 3-----jsert----agjyt----1212-----------3
> 4-----wqfh---jfgyiu----3434-----------1
> 5-----aghj----jqqre----3434-----------2
>
> How could I produce the "newColumn" ?
>
> Thanks
> Kurlan

"CBretana" wrote:

> There are several ways..  One which uses a correlated subquery is as follows:
> Select Id, Name,City, Number,
>     (Select Count(*) From Table
>      Where number = T.Number
>          And Id <= T.Id)
> From Table T
> Order By Id
>
>
> Other approaches use either a Temp Table, or a table variable, or a Created
> View to hold the Sount values, and then join that back to the main table to
> output the resutls...
>
>
>
>
> "Kutlan" wrote:
>
> > Hi Champs!
> > I have a tricky sql problem, and I would apreciate some help from you.
> >
> > In my SELECT statement my result looks like this:
> >
> > ID----name----city----number
> > 1 ---- lfkjdfl---djdjjd---1212
> > 2----- mdsf-- safta----1212
> > 3-----jsert----agjyt----1212
> > 4-----wqfh---jfgyiu----3434
> > 5-----aghj----jqqre----3434
> >
> >
> > But now I want a added column, in my SELECT result, so it would look like:
> >
> > ID----name----city----number---newColumn
> > 1 ---- lfkjdfl---djdjjd---1212----------1
> > 2----- mdsf-- safta----1212-----------2
> > 3-----jsert----agjyt----1212-----------3
> > 4-----wqfh---jfgyiu----3434-----------1
> > 5-----aghj----jqqre----3434-----------2
> >
> > How could I produce the "newColumn" ?
> >
> > Thanks
> > Kurlan

"Kutlan" wrote:

> Thank You!!! this really saved my day
>
>
> "CBretana" wrote:
>
> > There are several ways..  One which uses a correlated subquery is as follows:
> > Select Id, Name,City, Number,
> >     (Select Count(*) From Table
> >      Where number = T.Number
> >          And Id <= T.Id)
> > From Table T
> > Order By Id
> >
> >
> > Other approaches use either a Temp Table, or a table variable, or a Created
> > View to hold the Sount values, and then join that back to the main table to
> > output the resutls...
> >
> >
> >
> >
> > "Kutlan" wrote:
> >
> > > Hi Champs!
> > > I have a tricky sql problem, and I would apreciate some help from you.
> > >
> > > In my SELECT statement my result looks like this:
> > >
> > > ID----name----city----number
> > > 1 ---- lfkjdfl---djdjjd---1212
> > > 2----- mdsf-- safta----1212
> > > 3-----jsert----agjyt----1212
> > > 4-----wqfh---jfgyiu----3434
> > > 5-----aghj----jqqre----3434
> > >
> > >
> > > But now I want a added column, in my SELECT result, so it would look like:
> > >
> > > ID----name----city----number---newColumn
> > > 1 ---- lfkjdfl---djdjjd---1212----------1
> > > 2----- mdsf-- safta----1212-----------2
> > > 3-----jsert----agjyt----1212-----------3
> > > 4-----wqfh---jfgyiu----3434-----------1
> > > 5-----aghj----jqqre----3434-----------2
> > >
> > > How could I produce the "newColumn" ?
> > >
> > > Thanks
> > > Kurlan

:-)

"Kutlan" <Kut***@discussions.microsoft.com> wrote in message
news:C86612FB-17E9-4B54-BD03-CCCC113FE8A0@microsoft.com...
> Hi Champs!
> I have a tricky sql problem, and I would apreciate some help from you.
>
> In my SELECT statement my result looks like this:
>
> ID----name----city----number
> 1 ---- lfkjdfl---djdjjd---1212
> 2----- mdsf-- safta----1212
> 3-----jsert----agjyt----1212
> 4-----wqfh---jfgyiu----3434
> 5-----aghj----jqqre----3434
>
>
> But now I want a added column, in my SELECT result, so it would look like:
>
> ID----name----city----number---newColumn
> 1 ---- lfkjdfl---djdjjd---1212----------1
> 2----- mdsf-- safta----1212-----------2
> 3-----jsert----agjyt----1212-----------3
> 4-----wqfh---jfgyiu----3434-----------1
> 5-----aghj----jqqre----3434-----------2
>
> How could I produce the "newColumn" ?
>
> Thanks
> Kurlan

"Itzik Ben-Gan" <it***@REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23G6iw5RWFHA.3188@TK2MSFTNGP09.phx.gbl...
> Not that it helps you now, but this would be so much simpler and more
> efficient in SQL Server 2005:
>
> SELECT *, ROW_NUMBER() OVER(PARTITION BY number ORDER BY ID) AS rn
> FROM T1
>
> :-)