How to select only the next lower value

Question

I am trying to select a lower number from a database using SQL.

I have a table in which I have entries like this

ID NodeName NodeType 4 AA 2 BB 2 CC 1 DD 0 EE 

and other columns, such as name and type.

If I pass "4" as a parameter, then I want to get the following smallest entries of numbers:

 ID NodeName NodeType 2 BB 2 CC 

Right now, if I use the < sign, it gives me

 ID NodeName NodeType 2 BB 2 CC 1 DD 0 EE 

How can I make this work?

Answer 1

You can use WITH TIES :

 SELECT TOP (1) WITH TIES * FROM mytable WHERE ID < 4 ORDER BY ID DESC 

TOP combined with WHERE and ORDER BY selects the next lowest value of 4 . WITH TIES ensures that all of these values ​​are returned if there are more.

Demo here

Answer 2

 select ID from dbo.yourtable where ID in ( select top 1 ID from dbo.your_table where ID < 4 order by ID desc ); 

Note: where dbo.your_table is your source table

What it does, it uses an internal query to pull the next smallest ID below your selected value. Then the external query simply pulls all records that have the same match with the ID next smallest value.

Here is a complete working example:

 use TestDatabase; go create table dbo.TestTable1 ( ID int not null ); go insert into dbo.TestTable1 (ID) values (6), (4), (2), (2), (1), (0); go select ID from dbo.TestTable1 where ID in ( select top 1 ID from dbo.TestTable1 where ID < 4 order by ID desc ); /* ID 2 2 */