?
Solved

Adding sequence number for position in a group using MySQL query

Posted on 2010-09-22
8
Medium Priority
?
976 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:mcmahling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
8 Comments
 
LVL 4

Expert Comment

by:max-hb
ID: 33737423
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33738718
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
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 2000 total points
ID: 34622252
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Accepted Solution

by:
mcmahling earned 0 total points
ID: 34622744
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 34622815
I talked about that in my article that I linked you to.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 34622838
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

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question