loop and select with stored MySql

Question

I have the need to select the last enter for each Passcode for each department. Each department has a 3 digit passcode, each person is given a code based on department. i.e. dept A has numbers 000-099, dept B has 100-199, dept C 201-299 and so on upto 999.

The database holds name and passcode for each person. J smith 101 H frank 102 S saop 301 B Chesse 001 H roberts 401 K robert 402 b brety 403

I need a sorted that should loop over all results and return the last number from each dept (in the above case 102,301, 001, 403)

Not having a lot of joy, should be something like

    delimiter #

    BEGIN

    declare nmax int unsigned default 9;

    declare nmin int unsigned default 0;

    while nmin < nmax do

    select Passcode from main where Passcode < min*100 limit 1;

    set nmin = nmin+1;

    end while;

    END #

    delimiter ;

Just throws back 1064 error everytime?

Possible duplicate of [How can I fix MySQL error #1064?](https://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064) — tanaydin, Aug 01 '18 at 14:03
Error: sql error (1064) syntax to use near 'BEGIN declare nmax int unsigned default 9; declare nmin in unsigned default 0 at line 8 — , Aug 01 '18 at 14:43

tanaydin · Answer 1 · 2018-08-01T16:22:51.837

it seems you are missing "CREATE PROCEDURE" line here to me... Here my console output with similar declaration

MySQL [prime]> delimiter #
MySQL [prime]> CREATE PROCEDURE p1 ()
    -> BEGIN
    -> declare nmax int unsigned default 9;
    -> declare nmin int unsigned default 0;
    -> while nmin < nmax do
    -> select n from primes where n < n*100 limit 1;
    -> set nmin = nmin+1;
    -> end while;
-> END #
Query OK, 0 rows affected (0.000 sec)

MySQL [prime]> call p1();
Empty set (0.000 sec)
Empty set (0.000 sec)
Empty set (0.000 sec)
Empty set (0.001 sec)
Empty set (0.001 sec)
Empty set (0.001 sec)
Empty set (0.001 sec)
Empty set (0.001 sec)
Empty set (0.001 sec)
Query OK, 0 rows affected (0.001 sec)

I hope that helps.

score 0 · Answer 2 · answered Aug 20 '18 at 04:25

After fixing the syntax error, you will find a couple more problems.

What is min?
In tanaydin's answer check the usage of n; it is messed up.
LIMIT is useless without an ORDER BY
< should be >=

This is a variant on "groupwise max", which has other solutions -- without any looping.

loop and select with stored MySql

2 Answers2