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?
mcmahlingAsked:
Who is Participating?
 
mcmahlingConnect With a Mentor Author Commented:
I did try find_in_set  and Group_ConCat several times but I could not get it to give me the right answer. I found a solution that looks like

Set @counter = 0;

Select K.observation_date, K.Period_date, K.Period,  @prev := @curr, @curr := K.Period,
@counter := if (@prev = @curr, @counter := @counter + 1, @counter := 1),
@counter as rank, Abs(DateDiff(K.observation_date, K.Period_Date)) as Date_Diff from Results as K
(SELECT @curr := null, @prev := null, @rank := 0) sel1
Group By K.observation_date, K.Period, K.Period_date Order by K.obervation_date, rank
0
 
max-hbCommented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
You can probably simulate this with ranking:
Analytical SQL : Where do you rank? - http://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.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Please advise what happened when you tried the solutions we provided above.  If you had questions on them, it would have been appropriate to ask those here in the thread.  It is less appropriate to simply return after so long and delete.  
0
 
Kevin CrossChief Technology OfficerCommented:
I talked about that in my article that I linked you to.
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.