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