0

i have the table 'points_level' with :

id | description | level
--------------------------
1  | noob        | 0
2  | rookie      | 50
3  | The boss    | 100

i need to build a function that return the id of the level giving points as integer for example :

select calc_level(12)
result: 1

select calc_level(90)
result: 2

select calc_level(300)
result: 3

select calc_level(100)
result: 3

select calc_level(-50)
result: 1

i need to add some level in the points_level table (in the near future) so i shuldn't calculate the results via some simple "CASE WHEN" i think about a "CASE WHEN" in a for loop, i found some example for SSQL but nothing for MYSQL and i don't know the MYSQL SYNTAX for create this kind of function :(

somebody can help me ?

Thanks

ilmetu
  • 448
  • 11
  • 27
  • What you want is, give a level (a number), to find the id of the row which has the lower bound of the interval containing your number? – Cynical Jan 07 '13 at 14:07
  • yes and no, if i provide -50 or -10 it can return the near id from the level number ... (i dont speak english well, so i'm sorry if my english isn't good as yours) – ilmetu Jan 07 '13 at 14:33

1 Answers1

1

The algorithm is relatively simple - you need the id where the points supplied is greater than or equal to the level:

SELECT id
FROM points_level
WHERE 
  (<points_input> >= level)
  -- For negative input, return the lowest level
  OR (<points_input> < 0 AND level <= 0)
ORDER BY level DESC LIMIT 1

http://sqlfiddle.com/#!2/07601/4

Review the MySQL CREATE FUNCTION syntax for the proper way to wrap it in a function (if you feel you really need that).

CREATE FUNCTION calc_level (in_points INT)
RETURNS INT
BEGIN
    DECLARE id_out INT;
    SET id_out = (
      SELECT id
      FROM points_level
      WHERE
        in_points >= level 
        OR (in_points < 0 AND level <= 0)
      ORDER BY level DESC LIMIT 1
    );
    RETURN id_out;
END
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390