CODEWITHSUNDEEP
oracle-database
JavaQuest
JavaQuest

Reputation: 713

Combine two Select statements in one call

I am trying to query a table to find first the exact match to a column, in case the match fails; select the closest one in the range.

For e.g.

testTable

  id        width       cost

  1         10.2        100
  2         10.5        200
  3         10.1         50  

 Select * from testTable where width = 10.3;

in case this returns without record I want to go for the nearest one in the range of 10.1 to 10.9.

EDIT1: it's Oracle; updated the tag

Upvotes: 1

Views: 74

Answers (2)

Marek Vitek
Marek Vitek

Reputation: 1633

You would write something like this:

 select * from (
    Select * from testTable where width = 10.3
    union
    Select * from testTable where width > 10.1 and width < 10.9
    order by abs (10.3 - width) asc
) T LIMIT 1

And yes, it can be reduced to what Gordon Linoff showed

    Select * from testTable where width > 10.1 and width < 10.9
    order by abs (10.3 - width) asc
    LIMIT 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

In most databases you can do something like this:

Select *
from testTable
order by abs(width - 10.3)
fetch first 1 row only;

Some databases might use limit, top, or even where rownum = 1, but the idea is the same.

Upvotes: 3

Related Questions