Here's an example table
| ID | FROM | TO | MAX |
|---|---|---|---|
| 100001 | 100.00 | 199.99 | 5 |
| 100002 | 200.00 | 299.99 | 4 |
| 100003 | 300.00 | 399.99 | 3 |
| 100004 | 400.00 | 499.99 | 2 |
This SQL works just fine. The result is row with row ID 10001.
DECLARE @VAL DECIMAL(6,2);
SET @VAL = 159.97
SELECT ID, FROM, TO, MAX
FROM [MyTable].[dbo].[MagicPlan]
WHERE @VAL BETWEEN FROM AND TO;
But, I can't figure out how to default to the last row where @VAL exceeded the range. As it stands currently, changing @VAL to 695.43 returns no rows. I want it to return row ID 10004. Does that make sense?
CodePudding user response：
Use TOP 1 and first order by your desired criteria, then order by your secondary criteria
DECLARE @VAL decimal(6,2) = 159.97;
SELECT TOP 1 ID, [FROM], [TO], [MAX]
FROM [MyTable].[dbo].[MagicPlan]
ORDER BY
CASE WHEN @VAL BETWEEN [FROM] AND [TO] THEN 1 ELSE 0 END DESC
ID DESC;
Personally I would do it this way, because I find BETWEEN a bit unintuitive.
SELECT TOP 1 ID, [FROM], [TO], [MAX]
FROM [MyTable].[dbo].[MagicPlan]
ORDER BY
CASE WHEN [FROM] <= @VAL AND [TO] >= @VAL THEN 1 ELSE 0 END DESC
ID DESC;