selecting a target integer

[EckyThump]

Does anybody know how (if its possible) to select a number nearest a target number, from a range of numbers!
If I have a range of records:
Part Qty
A 2
A 10
A 45
A 200
B 3
B 11
B 67
B 999

I want to select the records that have a qty nearest to 10.

i.e. A 10
B 11
Is this possible, could anybody help.

Thanks

 

[Crowley16]

select top 1 *
from table
order by abs(qty - 10) desc

--------------------
Procrastinate Now!

 

[EckyThump]

This doesn't work properly.
There aer a large number of records that are a qty near 10, but this is only showing items with a qty of exactly 10, therefore missing the majority of parts.

Any more ideas?

 

[PHV]

Typed, untested (SQL code):
SELECT A.Part, A.Qty
FROM yourTable AS A INNER JOIN (
SELECT Part, Min(Abs(Qty-10)) AS MinDiff FROM yourTable GROUP BY Part
) AS M ON A.Part = M.Part
WHERE Abs(A.Qty-10) = M.MinDiff

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Crowley16]

well, if you don't want to have records which are 10, then just include a where <> 10 clause...

--------------------
Procrastinate Now!

 

[EckyThump]

PHV:
Many thanks, it works a treat!!
Crowley16:
Sorry I meant to say ONLY qty 10 was being selected and some other qty near 10 were not.

Sorted anyway.
Many thanks again to PHV