Limiting number of records returned

[exabyte]

Hi all, I normally use ColdFusion to limit the rows returned but I need to do it with SQL only this time. I need something like this:

SELECT id, keys
FROM thetable
WHERE keys LIKE '%fruit%'
UNION
SELECT id, keys
FROM thetable
WHERE keys LIKE '%apples%'

This seems to be stacking the "apples" records first, above the "fruit" records, which is what I want (though I'd like to implicitly state that this is what I want, if anyone has any pointers).

I also need the "fruit" part to only return 5 records, whereas the "apples" part should return everything it can get.

Will Oracle let me do this? I can't find it in the documentation anywhere but it would be something like LIMIT for MySQL.

Thanks in advance!

[pando]

try

SELECT id, keys
FROM thetable
WHERE keys LIKE '%fruit%'
and rownum < 6
UNION
SELECT id, keys
FROM thetable
WHERE keys LIKE '%apples%'

[exabyte]

You're brilliant, worked like a charm -- thanks!

[exabyte]

This works:

SELECT id, keys, id - 1000 "IDVal"
FROM thetable
WHERE keys LIKE '%fruit%'
and rownum < 6
UNION
SELECT id, keys, id + 1000 "IDVal"
FROM thetable
WHERE keys LIKE '%apples%'
ORDER BY "IDVal"



[Edited by exabyte on 09-19-2001 at 12:52 PM]