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
add comment

1 Answer

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
add comment

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.