SQL limit results in where clause

Question

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
)

In your edit, why are you comparing size to two different numbers? I thought N1 and N2 were the number of results to return, not sizes. — Barmar
– Barmar, Commented Mar 3, 2014 at 18:09
You're right, fixed it. My example is not very nice, since the original problem is way complicated. — Barium Scoorge
– Barium Scoorge, Commented Mar 3, 2014 at 18:12

Barmar · Accepted Answer · 2014-03-03 20:20:32Z

2

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

edited Mar 3, 2014 at 20:20

answered Mar 3, 2014 at 17:38

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barmar Over a year ago

@ObscurMoirage I've revised the answer to use Oracle syntax, and tested it at sqlfiddle.

Barium Scoorge Over a year ago

@GordonLinoff indeed it was an Oracle SQL question. I guess i've lost everyone when using the term "limit" which is also a MySQL keyword. Thanks for your changes Barmar

Gordon Linoff · Accepted Answer · 2014-03-03 17:47:34Z

2

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

answered Mar 3, 2014 at 17:47

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Barium Scoorge Over a year ago

Whats the difference between UNION and UNION ALL ?

Barmar Over a year ago

UNION removes duplicates, UNION ALL leaves them in. They're equivalent in this case, since the IDs are from disjoint subsets of the data. But ALL should make the query more efficient, since it doesn't have to check for duplicates.

Barium Scoorge · Accepted Answer · 2014-03-03 18:12:56Z

0

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
)

edited Mar 3, 2014 at 18:12

answered Mar 3, 2014 at 18:03

Barium Scoorge

2,0285 gold badges31 silver badges50 bronze badges

Collectives™ on Stack Overflow

SQL limit results in where clause

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related