You don't want to do that. Your memory temptable should have a primary key and it generally uses a hash index so you definitely don't want to use order by unless you explicitly create it to use btree index... And for such a small number of values, what is wrong with using a small number of comparisons? On Wed, 28 Jun 2023, 9:36 am manpritsinghece--- via discuss, < discuss@lists.mariadb.org> wrote:

Consider a problem where i have to find the second largest value in each row : I have tried to write code for that CREATE TABLE xyz(a INT, b INT, c INT); INSERT INTO xyz VALUES (3, 7, 5), (5, 6, 2), (9, 3, 6), (5, 1, 3), (2, 4, 7);

CREATE TEMPORARY TABLE tb(g INT); DELIMITER // CREATE FUNCTION second_largest(x INT, y INT, z INT) RETURNS INT BEGIN DECLARE ans INT; INSERT INTO tb VALUES (x), (y), (z); SET ans = (SELECT g FROM tb ORDER BY g DESC LIMIT 1, 1 ); DELETE FROM tb; RETURN ans; END // DELIMITER ;

SELECT second_largest(a, b, c) FROM xyz; SELECT second_largest(4, 1, 8);

The function is working ok ...

I just need to know if the way i have used the temporary table inside the function is ok or not _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org

Function calls are very cheap compared to access any table. Internal functions are cheapest. On Thu, 29 Jun 2023, 7:49 am manpritsinghece--- via discuss, < discuss@lists.mariadb.org> wrote:

This is something learning by doing. What about this function

CREATE FUNCTION second_largest(x INT, y INT, z INT) RETURNS INT DETERMINISTIC RETURN x+y+z - LEAST(x, y, z) - GREATEST(x, y, z);

See i believe in code clarity, although this seems using two function calls inside my function . may be expensive in terms of time, but is more clear . _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org