Closest match SQL query

I have a need to execute a query in T-SQL on a numeric field in a SQL table.

However if there is no exact match I'd likea query that will return the row that is just below my value.

As an example if the table has values: 1,2,4,5,7,9, 15 and 20 for instance and I am matching with a varibale containing the value 9 then I'd like it to return that row. however if my variable has the value 19 I want the row containign 15 returned.

Is this possible? Can anyone help me with such a query?

Regards

Clive

If you use <= in your WHERE clause, and an Order By ... DESC, you will get the results you want. When you use ExecuteScalar(), it will only return the first value in the resultset.|||

Here is one (SQL Server 2005):

SELECT NumFROM(SELECT Num, Row_Number()over(orderby NumDESC)as rNum

FROM yourTable

WHERE num<=19) t

WHERE rNum=1

Another one (should work with 2000):

SELECTTop 1 NumFROM(SELECT Num,(SELECTCOUNT(*)FROM yourTable bWHERE a.Num<=b.Num)as rNum

FROM yourTable a

WHERE Num<=19) t

OrderBY rNum

|||

SELECT TOP 1 *

FROM MyTable

WHERE Num<=19

ORDER BY Num DESC

|||

Thanks for your help. However I'm not doign too well, i've not managed to get these to work. I shoudl clarify that I am useing SQL 2005.

The cloest seems to be:

SELECT TOP 1 *

FROM table

WHERE id <=15

That does indeed return one row however since I can't guarantee the order of the data in the table I need to sort it first I think.

I have tried :

SELECT top 1 *

from (SELECT * from table ORDER BY id)

WHERE ID <=15 but also this fails.

Any help appreciated.

Regards

Clive

|||

Use Motley's code. You will see that it includes an order by clause. You need to Order By id DESC.

SELECT TOP 1 *

FROM table

WHERE id <=15

ORDER BY id DESC

|||

Thanks Mike,

That works perfectly - I was being dumb and putting the ORDER BY clause in the wrong place.

Clive