MySQL Procedure / MySQL Function

Question

I am still relatively new to MySQL and am stuck on a bit of data engineering.

I have a table with following:

Event_ID, Minutes, EventCode

I have multiple rows with same Event_ID and what event has occurred (eventcode) along with when in minutes (Minutes).

What I want to do is output to a new table the sequence of events based on the minutes for an event_id:

Eg:

Source:

Event_ID,   Minutes, EventCode
12,         45,         A
12,         49,         B
12,         78,         A

WOuld be transformed into:

12, 45, A, 1
12, 49, B, 2
12, 78, B, 3

So the last column shows the sequence. Although it can be assmed the source table is sorted by event_id following by minutes I would rather a solution that worked for it to be unsorted if possible

Some pointers would be great!

Thanks

which version are you using, if above 8.0 you can use the row_number() function — Bryan Dellinger
– Bryan Dellinger, Commented Feb 4, 2020 at 12:08
if under 8 here is a tutorial on how to emulate row_number() mysqltutorial.org/mysql-row_number — Bryan Dellinger
– Bryan Dellinger, Commented Feb 4, 2020 at 12:09

sticky bit · Accepted Answer · 2020-02-04 12:08:38Z

3

Im MySQL 8 and higher you can use the row_number() window function.

SELECT event_id,
       minutes,
       eventcode,
       row_number() OVER (PARTITION BY event_id
                          ORDER BY minutes)
       FROM elbat;

answered Feb 4, 2020 at 12:08

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Santosh Vishwakarma · Accepted Answer · 2020-02-04 13:09:37Z

0

Try this query: select event_id, minutes, eventcode, @rownum:=@rownum+1 No from elbat, (SELECT @rownum:=0) r;

answered Feb 4, 2020 at 13:09

Santosh Vishwakarma

19911 bronze badges

Collectives™ on Stack Overflow

MySQL Procedure / MySQL Function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related