CODEWITHSUNDEEP
mysqlselectpointsrank
mysqllearner
mysqllearner

Reputation: 13913

How to select in between?

I have a table, called Level.

id  |  level  |  points(minimum)  
-------------------------
1   |  1      |  0 
2   |  2      |  100 
3   |  3      |  200

Let say I have 189 points, how do i check which level the user in?

EDIT:

Best answer chosen. Now I am comparing the request by adding EXPLAIN before the SELECT query, i have this result:

id  |  select_type  |  table  |  type  |  possible_keys  |  key  |  key_len  |  ref  |  rows  |  Extra
-------------------------------------------------------------------------------------------------------------
1   |    SIMPLE     |  level  |   ALL  |       NULL      |  NULL |    NULL   |  NULL |  8   |  Using where


id  |  select_type  |  table  |  type  |  possible_keys  |  key  |  key_len  |  ref  |  rows  |  Extra
-------------------------------------------------------------------------------------------------------------
1   |    SIMPLE     |  level  |   ALL  |       NULL      |  NULL |    NULL   |  NULL |  8   |  Using where; Using filesort

How do i know which one is better or faster?

Upvotes: 1

Views: 126

Answers (4)

Kaleb Brasee
Kaleb Brasee

Reputation: 51965

If you're looking for the level that the player is currently in, you want to select the maximum level with a points requirement less than the points the player currently has:

select max(level) from level where points <= 189;

This may work better if each level has a min_points and max_points amount:

id | level | min_points | max_points
------------------------------------
1  |   1   | 0          | 99
2  |   2   | 100        | 199
3  |   3   | 200        | 299

Then your query wouldn't need to aggregate:

select * from level where min_points <= 189 && max_points > 189;

Edit: ugh, I keep messing up my SQL tonight, LOL.

Upvotes: 6

MadCoder
MadCoder

Reputation: 1352

select max(level) from level where points <= 189

This assumes the 'points' field in the table is the mininum points ot achieve that level.

Upvotes: 0

Matt
Matt

Reputation: 44078

This wouldn't require a 'between' or any kind of aggregate function at all.. you could just select all rows that are less than the points, sort descending, and then first row should be the correct one.

select level from Level where points <= 189 order by points desc limit 1

(Assuming MySQL .. if you do not have MySQL, 'limit' may not work)

Upvotes: 2

Dumb Guy
Dumb Guy

Reputation: 3446

I'm not quite sure what you mean but I think this is what you want:

SELECT `level` FROM `Level` WHERE `points`=189

Upvotes: 0

Related Questions