CODEWITHSUNDEEP
mysqlsqlnumberscompare
d199224
d199224

Reputation: 541

mySQL comparing numbers

I'm trying to figure out a way in mySQL to compare a number to a list of numbers to find the the closest one to it.

So in essence if I had the number 5 and the list of numbers 7,8,9,2,1 the answer would be 7.

Anyone got any ideas on how to do this?

Thanks

Here is what i need this for. I'm trying to find the nearest station that has a particular service when i provide the current station id that I am at and the service I am looking for.

Here is the code for my stored procedure:

    DROP PROCEDURE IF EXISTS nearest;
    DELIMITER //
    CREATE PROCEDURE nearest
    (
        IN serviceIn VARCHAR(20), IN stationid INT
    )
    BEGIN
        SELECT station_id
        FROM station
        WHERE ABS(station_id - stationid) = (SELECT MIN(ABS(station_id - stationid))
        FROM station) AND service = serviceIn;
    END //
    DELIMITER ;

Upvotes: 1

Views: 1739

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

You can do this:

SELECT number
FROM tablename
ORDER BY  ABS(number - 5) 
LIMIT 1;

See it in action

But this will always return only one number, but in case there is duplicate numbers that are close with the same number it will get only one of them. For instance if you have the number 3 in your list, in this case the two number 7 and 3 should be returned, the previous query will return only one of them.

For this case, try this:

SELECT number
FROM tablename
WHERE ABS(number - 5) = (SELECT MIN(ABS(number - 5))
                         FROM tablename);

See it in action:

Update:

You will need to add the condition `` to the subquery as well, so that your stored procedure should looks like this:

DROP PROCEDURE IF EXISTS nearest;

CREATE PROCEDURE nearest
(
  IN serviceIn VARCHAR(20), IN stationid INT
)
BEGIN
  SELECT station_id
  FROM station
  WHERE ABS(station_id - stationid) = (SELECT MIN(ABS(station_id - stationid))
                                       FROM station
                                       WHERE service = serviceIn) AND service = serviceIn;
END

Updated SQL Fiddle Demo

Upvotes: 2

Related Questions