CODEWITHSUNDEEP
mysql
Jaipal Singh
Jaipal Singh

Reputation: 59

MySQL Procedure / MySQL Function

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

Upvotes: 0

Views: 38

Answers (2)

Santosh Vishwakarma
Santosh Vishwakarma

Reputation: 199

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

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

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;

Upvotes: 3

Related Questions