MySql function and session / user variables inside the Mysql function

Question

I would like to create a MySql function that will return an incremental row count as long as the given id is the same and if the id changes function would reset the count starting from 1.

Below is a result I am looking for, where you can see as long as the itemId (on left column) remains the same, the Count on right column will increments, and when itemId changes the Count will restart from 1.

In my mind, the MySql function like the one below would do the incremental counting and resetting, but unfortunately it returns 1 for each row. My thought was to provide the current itemId to the function and the function would compare the sent in id to to the one saved in @n session variable from last row, and as long as the id's are the same the function would return incremented row count, else it would reset to 1. Can anybody guide me to why this function is not working? Or is there a better way to achieves the result I am looking for?

CREATE FUNCTION `nth`(id int) RETURNS tinyint(4)
BEGIN
declare ln tinyint;
if @saved_id = id then
    set @n := @n+1;
    set ln = @n;
else
    set @saved_id := id;
    set @n := 1;
    set ln = @n;
end if;
RETURN ln;
END

The Mysql version I am using is 5.7

Here is the example query I am using, the itemId is foreign key

select id, itemId, started_at 'Start', stopped_at Stop, nth(started_at) 'Count'
from events
order by itemId, stopped_at

This is called as row number over a partition of id. You can do it directly within a query. What is your MySQL server version ? — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 24, 2018 at 5:55
But how is the order defined in your table. Do you have a primary key in your table ? Without that this problem does not make sense as of now. Always remember that data is unordered set, and we need to specify the order, to define a deterministic row number — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 24, 2018 at 5:56
@Gunar, please answer this part: "But how is the order defined in your table. Do you have a primary key in your table ?" — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 24, 2018 at 19:07
The table has primary key. I order the data by itemId (foreign key). — Guntar
– Guntar, Commented Nov 24, 2018 at 19:20

Madhur Bhaiya · Accepted Answer · 2018-11-24 19:39:39Z

1

You don't need to define a UDF for this. You can achieve this within a SELECT query itself. In newer versions of MySQL (8.0.2 and above), it is achievable using ROW_NUMBER() OVER (PARTITION BY itemId ORDER BY id)

In older version, we can use the user-defined variables. In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same itemId values come together, with further sorting between them based on id.

Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the numbering ("count"). It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number ("Count") accordingly.

SELECT
  dt.id, 
  dt.Start,
  dt.Stop, 
  @rn := CASE WHEN dt.itemId = @itm THEN @rn + 1
              ELSE 1 
         END AS Count, 
  @itm := dt.itemId AS itemId 
FROM
(
  SELECT 
    id, 
    itemId, 
    started_at AS Start, 
    stopped_at AS Stop 
  FROM events
  ORDER BY itemID, id
) AS dt
CROSS JOIN (SELECT @itm := 0, @rn := 0) AS user_init_vars

answered Nov 24, 2018 at 19:39

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

3 Comments

Guntar Over a year ago

The takeaway here is, for it to work properly sub-query is needed which sorts records in the correct order, only then user-defined variable will save the real "previous row" value. Thank you for help.

Madhur Bhaiya Over a year ago

@Guntar also if you are interested in further understanding, you may check this discussion I had with fellow SO member: stackoverflow.com/questions/53404473/…

Madhur Bhaiya Over a year ago

@Guntar also following answer has more explanation: stackoverflow.com/a/53465139/2469308

Collectives™ on Stack Overflow

MySql function and session / user variables inside the Mysql function

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related