Retrieve record closest to x

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Retrieve record closest to x

Post by papa »

Hello,

Say I have a table with
id | points
1 | 1999
2 | 2000
3 | 3000
4 | 4000

The user submits a point value. Say for example he/she submits 250, how do I match that with my id 1. Or maybe the user submits 3700, then I want id 3 to be fetched and not 4.

How do I go about this?

thanks

edit:

What I got so far works, but if you submit 2800 for example. It fetches id 3 and not 2.

Code: Select all

            $sql = "SELECT ABS(points - $points) as distance, max_tot, lords, heroes, core, special, rare
                FROM a_army_size ORDER BY distance LIMIT 1";
User avatar
lord_webby
Forum Commoner
Posts: 44
Joined: Wed Aug 19, 2009 9:01 am

Re: Retrieve record closest to x

Post by lord_webby »

How about:
Id use a php switch and forget the sql comparison. Something like:

Code: Select all

 
<?php
$points = 370;
 
switch ($points) {
    case ($points < 2000):
        $i =1;
        break;
    case (1999 < $points < 3000):
        $i =2;
        break;
   case (2999 < $points < 4000):
        $i =3;
        break;
   Default:
        $i =4;
        break;
 
}
?>
 
Then:

Code: Select all

 
 $sql = "SELECT * FROM a_army_size WHERE ID = '$i' ORDER BY distance LIMIT 1";
 
Does that help?
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Retrieve record closest to x

Post by papa »

Thanks for your answer, though I'd prefer using mySQL for this as I'd need to change in several places if I did an update in the mysql table for example.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Retrieve record closest to x

Post by jackpf »

I'm not entirely sure how to do this, but you could subtract the value from the field, and then select whichever one is closest to 0.

So, say the value is 2200, 2000 will become -200 which is closest to 0.

Although, you'll have to make some sort of case for two values equidistant from 0. I suppose you could combine it with MAX() to select the highest value, closest to 0...so it'll round up.

This may not be the best way of doing it...or even possible, but that's all I can think of.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Retrieve record closest to x

Post by pickle »

So you want to round up if the value is smaller than the smallest value in the DB, and round down otherwise?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Retrieve record closest to x

Post by VladSun »

Like this:
[sql]SELECT    *FROM    a_army_sizeWHERE     points = COALESCE    (        (SELECT max(points) FROM a_army_size WHERE points < 2700),        (SELECT min(points) FROM a_army_size)    )LIMIT 1[/sql]
?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Retrieve record closest to x

Post by papa »

pickle wrote:So you want to round up if the value is smaller than the smallest value in the DB, and round down otherwise?
Jepp correct.

@Vlad: Unfortunately I get an errror: "FUNCTION army_builder.COALESCE does not exist"
I'm running Mysql 5.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Retrieve record closest to x

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
User avatar
lord_webby
Forum Commoner
Posts: 44
Joined: Wed Aug 19, 2009 9:01 am

Re: Retrieve record closest to x

Post by lord_webby »

Maybe:

Code: Select all

 
SELECT * FROM army_builder WHERE points < '$points' AND (('$points' - points) < 1000)  ORDER BY ID DESC LIMIT 1
 
But thinking on it, it won't work for any values under 999.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Retrieve record closest to x

Post by VladSun »

papa wrote:@Vlad: Unfortunately I get an error: "FUNCTION army_builder.COALESCE does not exist"
I'm running Mysql 5.
Blah!!!

Remove the new line before the opening bracket after COALESCE like this:

[sql]SELECT    *FROM    a_army_sizeWHERE     points = COALESCE(        (SELECT max(points) FROM a_army_size WHERE points < 2700),        (SELECT min(points) FROM a_army_size)    )LIMIT 1[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Retrieve record closest to x

Post by papa »

Thanks Vlad!

It works almost perfectly.

id points max_tot lords heroes core special rare
1 1999 3 0 3 2 3 1
2 2000 4 1 4 3 4 2
3 3000 6 2 6 4 5 3
4 4000 8 3 8 5 6


If I put in 2000 points i still display records from id 1. If points is 2001 it works. Same for 3000 and 4000.

Code: Select all

 
$sql = "SELECT
    *
FROM
  a_army_size
WHERE
 points = COALESCE(
(SELECT max(points) FROM a_army_size WHERE points < $points),
(SELECT min(points) FROM a_army_size)
)
LIMIT 1";
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Retrieve record closest to x

Post by VladSun »

So ... what do you suggest :P
C'mon - that's too easy!
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Retrieve record closest to x

Post by papa »

Haha sorry, got lazy :)

<=

Thanks again!
Post Reply