asked on

Adding sequence number for position in a group using MySQL query

I am trying to write a MySql query where I group and order items a date. I want to give a sequence number to the item in the group because I need that sequence number for another operation. Does any one have any suggestions?

max-hb

Hi!
Unfortunately MySQL does not support sequences. The best you can do is
a) calculate the sequence numbers within your application using the programming language of your choice
or
b) use MySQL user variables which is a little bit tricky, see comments on this page: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

CU
maxhb

Kevin Cross

You can probably simulate this with ranking:
Analytical SQL : Where do you rank? - https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

Aside from SQL syntax that will work in most environments the same, I have included a "MySQL Emulates The Best of Them." section which shows a trick with GROUP_CONCAT() and FIND_IN_SET() as well as one using ROWNUM() which is a user defined variable as mentioned in choice (b) above.

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

mcmahling

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

I talked about that in my article that I linked you to.

Kevin Cross

At any rate, let's say you didn't get that answer from us, then you could post what you used as a solution like you did and accept your comment as the answer versus just deleting (in the future).

In this case, I hope you go back and look to see that I explained all about that in my article in case it can be simplified or cleaned up some.