Solved

Adding sequence number for position in a group using MySQL query

Posted on 2010-09-22
8
955 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
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 59

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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 59

Expert Comment

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

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 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