CODEWITHSUNDEEP
mysqlsqldatabase
jbrulmans
jbrulmans

Reputation: 1005

SQL: Find closest number to given value with ties

I'm trying to find the closest number(s) to a given value in SQL. I have already made my query for multiple results:

SELECT *
FROM Cars
ORDER BY ABS(price - $price)

I know I can limit the table by using LIMIT 1, by which I have one number closest to the given value. But how can I include ties? Like for example when there are three or four cars with the same price? The amount of cars which have the same price is dynamic, so I can't specify a certain LIMIT.

I also know I could use SELECT TOP 1 WITH TIES, but I can't use this query because my database driver doesn't allow it. Does anybody have another idea of how to accomplish this?

Example:

When I want the cars closest to 3000, the query should return:

But without using a static LIMIT in the query, because the amount of cars with the same price can be different every time. Thanks

Upvotes: 1

Views: 3044

Answers (1)

StephaneM
StephaneM

Reputation: 4899

If your driver supports nested queries:

SELECT * 
FROM CARS 
WHERE ABS(price - $price) = ( SELECT MIN(ABS(price - $price)) FROM CARS )

Upvotes: 8

Related Questions