Hello!
I want to select some values, if another "maxvalue" within the row is higher than the input value.
If there exists more than one value higher than the input value, I want to select
the minimum among the higher values.
If no higher value exists, I want to select the value 0.
Table:
Code:
ID, value1, value2, maxvalue
The rows:
Code:
1 aaa bbb 9
2 ccc ddd 11
3 eee ffff 12
4 ggg hhh 20
Query:
Code:
Declare @input INT
SET @input = 10
SELECT value, MIN(maxvalue) FROM Table
WHERE maxvalue> @input
GROUP BY value
This keeps returning 11, 12 and 20.
The group by clause ignores the MIN, or skips it, whatever...
I want it to return this row: 2 ccc ddd 11.
Edit: Query for the temptable above:
Code:
DECLARE @tt TABLE(ID int, value nvarchar(5), value2 nvarchar(5), maxvalue int)
INSERT INTO @tt(ID, value, value2, maxvalue)
VALUES (1, 'aaa', 'bbb', 9), (2, 'ccc', 'ddd', 11), (3, 'eee', 'fff', 12), (4, 'ggg', 'hhh', 15)
Solved:
Code:
SELECT * FROM @tt
WHERE maxvalue =
(
SELECT MIN(maxvalue) FROM @tt
WHERE maxvalue > @input
)
dBforums
>
Selecting the closest higher value
07-27-11, 13:33
Linear Mode
