CODEWITHSUNDEEP
sqloracle-database
Barium Scoorge
Barium Scoorge

Reputation: 2008

SQL limit results in where clause

Assuming I have the following table :

Table Files
 id
 size

In pseudo SQL I need this king of processing :

select id
where
size < S1 and (limit this clause to N1 results)
or
size > S2 and (limit this clause to N2 results)

I know Oracle defines rownum keyword to limit results.

But in my case, that does not work.

Do this require subselects ? How ? Is that possible to use multiple subselects ?

(This SQL would be generated by a java program with other where clauses, making the use of subselects difficult..)

Edit: I need to filter more than just different size.

SOLVED

SELECT * 
FROM Files
WHERE
    someField = 'stuff'
AND
    someOtherField = 'other stuff'
AND
(
    SELECT id FROM Files WHERE size <= S1 AND ROWNUM <= N1
    UNION
    SELECT id FROM Files WHERE size > S2 AND ROWNUM <= N2
)

Upvotes: 0

Views: 475

Answers (3)

Barmar
Barmar

Reputation: 780843

Use a UNION:

SELECT *
FROM (
    SELECT 'low' AS "which", "id"
    FROM Files
    WHERE "size" < S1)
WHERE ROWNUM <= N1

UNION

SELECT *
FROM (
    SELECT 'high' AS "which", "id"
    FROM Files
    WHERE "size" >= S1)
WHERE ROWNUM <= N2

DEMO

Upvotes: 2

Barium Scoorge
Barium Scoorge

Reputation: 2008

Solved using this query

SELECT * 
FROM Files
WHERE
    someField = 'stuff'
AND
    someOtherField = 'other stuff'
AND
(
    SELECT id FROM Files WHERE size <= N1  AND ROWNUM <= N1
    UNION
    SELECT id FROM Files WHERE size  > N2  AND ROWNUM <= N2
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Assuming the clauses are independent (which they are for your example), you can do this with union all:

select id
. . .
where size < S1 and rownum <= N1
union all
select id
. . .
where size > S1 and rownum <= N2

If you don't want to repeat the from clause (because it is complex for instance), you can use a CTE:

with t as (<blah blah blah>)
select *
from ((select id from t where size < S1 and rownum <= N1) union all
      (select id from t where size > S1 and rownum <= N2)
     ) x

Upvotes: 2

Related Questions