How to select only the next smaller value

Question

I am trying to select smaller number from the database with the SQL.

I have table in which I have records like this

ID NodeName NodeType
4  A        A
2  B        B
2  C        C
1  D        D
0  E        E

and other columns like name, and type.

If I pass "4" as a parameter then I want to receive the next smallest number records:

ID NodeName NodeType
2  B        B
2  C        C

Right now if I am using the < sign then it is giving me

ID NodeName NodeType
2  B        B
2  C        C
1  D        D
0  E        E

How can I get this working?

Answer 1

You can use WITH TIES clause:

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

TOP clause in conjunction with WHERE and ORDER BY selects the next smallest value to 4. WITH TIES clause guarantees that all these values will be returned, in case there is more than one.

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 this does it uses an inner query to pull the next smallest ID below your selected value. Then the outer query just pulls all records that have that same match to the ID of the next smallest value.

Here's a full 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
*/