Counting result

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
KurlanSELECT 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
--|||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|||Thank You!!! this really saved my day
"CBretana" wrote:
> There are several ways.. One which uses a correlated subquery is as follo
ws:
> 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 Create
d
> View to hold the Sount values, and then join that back to the main table t
o
> output the resutls...
>
>
> "Kutlan" wrote:
>|||Thank You!!! this really saved my day
"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
> --
>
>|||Yr welcome !
"Kutlan" wrote:
> Thank You!!! this really saved my day
>
> "CBretana" wrote:
>|||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
:-)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Kutlan" <Kutlan@.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|||It would be much simpler with the RAC utility for S2k NOW:).
Sql99 ranking functions row_number,rank and dense_rank with
partitioning/ordering
have been in available in RAC for years!...and some additional functions MS
obviously
hasen't thought about :)...scary:)
www.rac4sql.net
"Itzik Ben-Gan" <itzik@.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
> :-)