Numbering duplicates

[markgargan]

Hi folks,

Say I have the following table

id | name
---------
1 Michael
2 James
3 John
4 James
5 Michael
6 James

Is there any select function I can perform that
could print out the values of the id, name column and a number representing the ith time this name appears
in the column? i.e. the result set would look like this.

id | name | ith
---------------
1 Michael 1
2 James 1
3 John 1
4 James 2
5 Michael 2
6 James 3


Sorry for the poor explanation I can't think of the proper term for this ith column? Version? Sequence number? Appearance count?

Thanks,
Mark.

 

[blom0344]

Mark,

It is probably not meant for the purpose, but test this:

Select 
rank() over (partition by name order by id asc) as ith
from table
order by name

Ties Blom
Information analyst

 

[markgargan]

Hi Ties,

Well that is perfect Ties. Thanks a million!!
Would you know of anywhere were I could get a list and a few
samples of similar functions? I was attempting it with a subselect and the ROW_NUMBER() function to no avail.

Thanks again,
Mark.

 

[blom0344]

Mark,

Look here:

http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM

Be prepared to be impressed

Ties Blom
Information analyst

 

[markgargan]

*Blub* *blub* it's soooo pretty... SOOOO pretty.
I promised myself I wouldn't cry..

Thanks a thousand times Ties,
Mark.