SQL Query to Select closest of 5 numbers

[Rick Bray]

I have a query which performs a calculation and gets a number. I then need to find which of these numbers (5, 10, 20, 50, 100) that number is closest to.

I would like to do this in one query. I would think it might have to be a nest query, but that is not a strength of mine.

For example:

A = 50
B = 1.578
C = A * B (78.9)

What number is C closest to: 5, 10, 20, 50, or 100?

Return 100

I have to do this for multiple rows which is why I want to do this with a single query.

Thanks

[rpresser]

Here you are. The case expression at the end handles the numbers that are exactly halfway between, and chooses the lesser; otherwise you'd have two rows each for such items.

CREATE VIEW Matchnumbers (N) AS
SELECT 5 AS N UNION SELECT 10 UNION SELECT 20 UNION SELECT 50 UNION SELECT 100
GO

SELECT ID, C, N AS Closest FROM (
SELECT ID, C, N, RANK() OVER (PARTITION BY ID ORDER BY ABS(C-N)) Z
FROM someTable, MatchNumbers
) Y WHERE Z = 1 AND N =
CASE WHEN C =7.5 THEN 5
WHEN C =15.0 THEN 10
WHEN C =20.0 THEN 20
WHEN C =75.0 THEN 50
ELSE N END
ORDER BY ID

[Gert-Jan Strik]

Post by Rick Bray
I have a query which performs a calculation and gets a number. I then need to find which of these numbers (5, 10, 20, 50, 100) that number is closest to.
I would like to do this in one query. I would think it might have to be a nest query, but that is not a strength of mine.
A = 50
B = 1.578
C = A * B (78.9)
What number is C closest to: 5, 10, 20, 50, or 100?
Return 100
I have to do this for multiple rows which is why I want to do this with a single query.
Thanks

Try this:

create table #groups (id int,lbound decimal(11,1),ubound decimal(11,1))
insert into #groups values (5, -2000000000, 7.5)
insert into #groups values (10, 7.5, 15)
insert into #groups values (20, 15, 35)
insert into #groups values (50, 35, 75)
insert into #groups values (100, 75, 2000000000)

create table #probe (probe decimal(12,4))
insert into #probe values (50)
insert into #probe values (1.578)
insert into #probe select EXP(SUM(LOG(probe))) from #probe
insert into #probe values (75)

-- Approach 1. Assumes non-overlapping groups
SELECT probe, id
FROM #probe
JOIN #groups
ON probe >= lbound
AND probe < ubound

-- Approach 2. Calculated, arbitrary pick for ties
SELECT probe, id
FROM (
SELECT probe, id, ROW_NUMBER() OVER (PARTITION BY probe ORDER BY
ABS(probe - id)) AS rn
FROM #probe
CROSS JOIN #groups
) DT
WHERE rn=1

drop table #probe
drop table #groups