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, Nov 24 '18 at 05: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, Nov 24 '18 at 05: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, Nov 24 '18 at 19:07
The table has primary key. I order the data by itemId (foreign key). — Guntar, Nov 24 '18 at 19:20

score 1 · Accepted Answer · answered Nov 24 '18 at 19:39

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

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. — Guntar, Nov 25 '18 at 18:28
@Guntar also if you are interested in further understanding, you may check this discussion I had with fellow SO member: https://stackoverflow.com/questions/53404473/selecting-best-row-in-each-group-based-on-two-columns/53405484#comment93754272_53405484 — Madhur Bhaiya, Nov 25 '18 at 18:30
@Guntar also following answer has more explanation: https://stackoverflow.com/a/53465139/2469308 — Madhur Bhaiya, Nov 25 '18 at 18:32

MySql function and session / user variables inside the Mysql function

1 Answers1