A very complicated UPDATE query

[sabavno]

Here is my problem:

The table looks like that:

Name Count

Joe

Polina

Joe

Nick

Polina

Nick

Nick

Joe 2
Polina 2
Nick 3

The last three lines represent the count of how many times each name appears in the table.

So, what I need is UPDATE QUERY to fill the rest of the COUNT column with the count of how many times this name has appeared so far

The result should look like that:

Name Count

Joe 1

Polina 1

Joe 2

Nick 1

Polina 2

Nick 2

Nick 3

Joe 2
Polina 2
Nick 3



PLEASE HELP ME WITH THIS....I AM DESPERATE

 

[r937]

you make it sound like Joe 1 and Joe 2 are somehow distinguishable by their position within the table

they aren't

there is no position in a relational table

the only order you will get is when you supply an ORDER BY clause to a SELECT statement

if there is some other column in the table that can be used for sequencing, then you would be best to write a stored procedure or similar script to apply the numbers to the rows after you have retrieved them in sequence

there is no easy ansi sql solution either -- perhaps you might want to re-post the question in the forum for your particular database system

rudy

 

[netangel]

After you add and ID field (to allow you to order your columns, you can do something like:

SELECT 	MyID, 
	MyName, 
	(SELECT COUNT(*) + 1 
	 FROM MyTable T2 
	 WHERE T2.MyID < MyTable.MyID AND T2.MyName = MyTable.MyName)
FROM MyTable

Hope this helps.
NetAngel