CODEWITHSUNDEEP
sqloracle-database
Lyle Wang
Lyle Wang

Reputation: 69

How to compare a value with two record of Oracle in the same time?

I have a table like below:

enter image description here

The record of id = 1, means that value >0 and value <=200.

The record of id = 2, means that value >200 and value <=1500.

The values are ordered by such analogy.

Now I have a value of 4000, I want to get the corresponding id value(should be 4).

Are there better way to write SQL compare with?

select * from table_name where value >=4000 and rownum = 1;

Upvotes: 1

Views: 115

Answers (2)

Giovanni
Giovanni

Reputation: 4015

Here is a simnple test case with analytic functions

create table test (id number, value number);
insert into test values(1,200);
insert into test values(2,1500);
insert into test values(3,3200);
insert into test values(4,4500);
commit;
select * from (
 SELECT id, value as min, 
     LAG(value, 1, 0 ) OVER (ORDER BY id desc) as max from test
     order by id) 
where 4000>=min AND 4000<max

The inner query uses LAG to provide a view that exposes the data as a range you can easily query

Upvotes: 2

Jeremy C.
Jeremy C.

Reputation: 2465

SELECT MAX(id)
FROM table
WHERE value <= 4000

If you want all the data (id and value)

SELECT *
FROM table
WHERE id = (SELECT MAX(id)
            FROM table
            WHERE value <= 4000)

NOTE: this will only work if your values are in order (meaning the value for id 1 is smaller than the value for id 2, value for id 2 is smaller than value for id 3 etc...)

Upvotes: 0

Related Questions