Solved

Adding sequence number for position in a group using MySQL query

Posted on 2010-09-22
8
969 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 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
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…
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…

724 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