Results 1 to 1 of 1
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: Selecting the closest higher value

    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
    )
    Last edited by ManyTimes; 07-27-11 at 14:42.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •