MySql function and session / user variables inside the Mysql function





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







1















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.



enter image description here
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









share|improve this question




















  • 1





    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 ?

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 5:55






  • 1





    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

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 5:56













  • The MySql version is 5.7

    – Guntar
    Nov 24 '18 at 15:13













  • @Gunar, please answer this part: "But how is the order defined in your table. Do you have a primary key in your table ?"

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 19:07






  • 1





    The table has primary key. I order the data by itemId (foreign key).

    – Guntar
    Nov 24 '18 at 19:20


















1















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.



enter image description here
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









share|improve this question




















  • 1





    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 ?

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 5:55






  • 1





    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

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 5:56













  • The MySql version is 5.7

    – Guntar
    Nov 24 '18 at 15:13













  • @Gunar, please answer this part: "But how is the order defined in your table. Do you have a primary key in your table ?"

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 19:07






  • 1





    The table has primary key. I order the data by itemId (foreign key).

    – Guntar
    Nov 24 '18 at 19:20














1












1








1








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.



enter image description here
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









share|improve this 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.



enter image description here
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






php mysql sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 25 '18 at 3:29







Guntar

















asked Nov 24 '18 at 0:23









GuntarGuntar

7519




7519








  • 1





    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 ?

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 5:55






  • 1





    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

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 5:56













  • The MySql version is 5.7

    – Guntar
    Nov 24 '18 at 15:13













  • @Gunar, please answer this part: "But how is the order defined in your table. Do you have a primary key in your table ?"

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 19:07






  • 1





    The table has primary key. I order the data by itemId (foreign key).

    – Guntar
    Nov 24 '18 at 19:20














  • 1





    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 ?

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 5:55






  • 1





    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

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 5:56













  • The MySql version is 5.7

    – Guntar
    Nov 24 '18 at 15:13













  • @Gunar, please answer this part: "But how is the order defined in your table. Do you have a primary key in your table ?"

    – Chowkidar Madhur Bhaiya
    Nov 24 '18 at 19:07






  • 1





    The table has primary key. I order the data by itemId (foreign key).

    – Guntar
    Nov 24 '18 at 19:20








1




1





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 ?

– Chowkidar Madhur Bhaiya
Nov 24 '18 at 5:55





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 ?

– Chowkidar Madhur Bhaiya
Nov 24 '18 at 5:55




1




1





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

– Chowkidar Madhur Bhaiya
Nov 24 '18 at 5:56







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

– Chowkidar Madhur Bhaiya
Nov 24 '18 at 5:56















The MySql version is 5.7

– Guntar
Nov 24 '18 at 15:13







The MySql version is 5.7

– Guntar
Nov 24 '18 at 15:13















@Gunar, please answer this part: "But how is the order defined in your table. Do you have a primary key in your table ?"

– Chowkidar Madhur Bhaiya
Nov 24 '18 at 19:07





@Gunar, please answer this part: "But how is the order defined in your table. Do you have a primary key in your table ?"

– Chowkidar Madhur Bhaiya
Nov 24 '18 at 19:07




1




1





The table has primary key. I order the data by itemId (foreign key).

– Guntar
Nov 24 '18 at 19:20





The table has primary key. I order the data by itemId (foreign key).

– Guntar
Nov 24 '18 at 19:20












1 Answer
1






active

oldest

votes


















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





share|improve this answer
























  • 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: stackoverflow.com/questions/53404473/…

    – Chowkidar Madhur Bhaiya
    Nov 25 '18 at 18:30











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

    – Chowkidar Madhur Bhaiya
    Nov 25 '18 at 18:32












Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53454163%2fmysql-function-and-session-user-variables-inside-the-mysql-function%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









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





share|improve this answer
























  • 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: stackoverflow.com/questions/53404473/…

    – Chowkidar Madhur Bhaiya
    Nov 25 '18 at 18:30











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

    – Chowkidar Madhur Bhaiya
    Nov 25 '18 at 18:32
















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





share|improve this answer
























  • 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: stackoverflow.com/questions/53404473/…

    – Chowkidar Madhur Bhaiya
    Nov 25 '18 at 18:30











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

    – Chowkidar Madhur Bhaiya
    Nov 25 '18 at 18:32














1












1








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





share|improve this answer













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






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 24 '18 at 19:39









Chowkidar Madhur BhaiyaChowkidar Madhur Bhaiya

19.8k62336




19.8k62336













  • 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: stackoverflow.com/questions/53404473/…

    – Chowkidar Madhur Bhaiya
    Nov 25 '18 at 18:30











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

    – Chowkidar Madhur Bhaiya
    Nov 25 '18 at 18:32



















  • 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: stackoverflow.com/questions/53404473/…

    – Chowkidar Madhur Bhaiya
    Nov 25 '18 at 18:30











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

    – Chowkidar Madhur Bhaiya
    Nov 25 '18 at 18:32

















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





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: stackoverflow.com/questions/53404473/…

– Chowkidar Madhur Bhaiya
Nov 25 '18 at 18:30





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

– Chowkidar Madhur Bhaiya
Nov 25 '18 at 18:30













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

– Chowkidar Madhur Bhaiya
Nov 25 '18 at 18:32





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

– Chowkidar Madhur Bhaiya
Nov 25 '18 at 18:32




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53454163%2fmysql-function-and-session-user-variables-inside-the-mysql-function%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







這個網誌中的熱門文章

Xamarin.form Move up view when keyboard appear

Post-Redirect-Get with Spring WebFlux and Thymeleaf

Anylogic : not able to use stopDelay()