[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Adding sequence number for position in a group using MySQL query

Posted on 2010-09-22
8
Medium Priority
?
991 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
  • 4
6 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

873 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