Row count as column

Databases
1

I was wondering if anyone had run into the following situation:


table_id | foreign_key1 | foreign_key2

I have rows that look like this:


1 | 1 | 2
1 | 1 | 3
..

The foreign_key2 is the unique value. What I would like to do is have a sequential count of where table_id and foreign_key1 are the same.

So for the above I would get something like:


1 | 1 | 2 | 1
1 | 1 | 3 | 2

In Oracle I could call the ROW_NUMBER() function. Is there anything similar to that in MySQL? My searches have only brought back stored procedures that won’t work for my situation.

2

when you say “have” does this means you want to generate the count in the output of a query, or store the count in a 4th column in the table?

if you mean the former, don’t do it with sql – do the count in whatever application language you’re using to display the result set

if you mean the latter, don’t do it at all – never store something that can be obtained so easily with an extraction (and that can potentially change)

that said, if you want to store the sequence number, you actually can do that with mysql auto_increments

helps?

3

I mean the former. It can’t be an auto_increment because the count would be reset after each set of groupings.


1 | 1 | 2 | 1
1 | 1 | 3 | 2
2 | 2 | 6 | 1
2 | 2 | 7 | 2
2 | 2 | 8 | 3
4

it ~can~ be an auto_increment, and reset after each grouping, if that’s what you want, but they will be permanent, and renumbering them will be difficult

but if what you really want is just to number them in a query, then you should do that in the application code

5

I think they can be permanent, but will need to double check. I didn’t think that MySQL’s auto_increment function could be used across three columns.

6

to be honest, i haven’t tested it on three, just on two, like in da manual

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

myisam and bdb tables only, though