bdristan bdristan - 8 months ago 31
SQL Question

SQL query to select equal or less than or greater than

Assuming that there is a table t with the following columns: Code int, Name nvarchar(50).

I'd would like to query the table for the most matching row for a given Code c. The 'most matching' criteria (in order of importance):


  • 1) select a row whose Code matches c

  • 2) select a row whose Code is greater than c (but the very first one). For example, if c = 4 and t
    contains 1, 2, 3, 5, 6, and 7, I'd like to select 5.

  • 3) select a row whose
    Code is less than c. For example, if c = 4 and t contains 3, 2, and
    1, I'd like to select 3.



The code is going to be in a stored procedure.

Could someone please suggest how to accomplish the above.

Thanks.

Sample data and expected results:

1, "Name1"
2, "Name2"
4, "Name4"
5, "Name5"

If c=2, result: 2,"Name2"
If c=3, result: 4,"Name4"
if c=6, result: 5,"Name5"

Answer

I'd order the rows by two criteria - the absolute distance from the target number and whether it's greater or lesser than it, and just pick the top row. E.g., assuming the target code is 4:

SELECT   TOP 1 *
FROM     t
ORDER BY ABS(code - 4) ASC, CASE WHEN code > 4 THEN 1 ELSE 0 END DESC