Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to fetch nearest rates for a given value from below table.

mysql> select * from rates;
+------+----------+----------+
| ID   |   Type   |   Rate   |
+------+---------------------+
| 1    |    A     |    12    |
| 2    |    A     |    18    |
| 3    |    A     |    25    |
| 4    |    A     |    27    |
| 5    |    A     |    34    |
| 6    |    A     |    38    |
| 7    |    B     |    40    |
| 8    |    B     |    43    |
| 9    |    B     |    50    |
| 10   |    A     |    55    |
| 11   |    A     |    58    |
| 12   |    A     |    62    |
+------+---------------------+

For example I want to get rates nearest to 12 it should return 12, 18, 25 and like wise nearest to 25 are 18,25,27

I tried many ways but I'm unsuccessful.

SELECT ID,
       TYPE,
       Rate
FROM (
        (SELECT *
         FROM
           (SELECT *
            FROM rates
            ORDER BY Rate DESC) a
         WHERE a.Type ='A'
           AND a.Rate <=25 LIMIT 0 ,
                                 1)
      UNION
        (SELECT *
         FROM
           (SELECT *
            FROM rates
            ORDER BY Rate ASC) b
         WHERE b.Type ='A'
           AND b.Rate >25 LIMIT 0 ,
                                3)) r
GROUP BY r.Rate
ORDER BY r.Rate ASC LIMIT 3

It gives me 25, 27, 34 but in fact it should return me 18,25,27. Any suggestions?

share|improve this question

2 Answers 2

up vote 2 down vote accepted
SELECT id, type, rate
FROM
( ( SELECT id, type, rate, 25-rate AS diff
    FROM rates
    WHERE type = 'A'
      AND rate < 25
    ORDER BY rate DESC
      LIMIT 3
  ) 
  UNION ALL
  ( SELECT id, type, rate, rate-25 AS diff
    FROM rates
    WHERE type = 'A'
      AND rate >= 25
    ORDER BY rate ASC
      LIMIT 3
  ) 
) AS tmp
ORDER BY diff
LIMIT 3 ;

Test at SQL-Fiddle

share|improve this answer

Could be done much simpler, I guess;

select id, type, rate, abs(25 - rate) as delta
from rates
order by delta
LIMIT 3
share|improve this answer
    
Correct. But not so efficiently. –  ypercube Nov 26 at 16:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.