| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: how to write this sql?
okay, so I left out handling nulls.
Here:
select
distinct 'select x, max(rownum) from abc where x = '''||x||''' and rownum
<=100 group by x UNION ALL'
from ABC where x is not null
UNION ALL
select
'select NULL, max(rownum) from abc where x is NULL order by 1;'
from dual order by 1 desc;
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham
Sent: Monday, July 19, 2004 6:27 PM
To: oracle-l_at_freelists.org
Subject: RE: how to write this sql?
Well, come on. Sure there is. It is quite UN-reasonable, but:
select distinct 'select max(rownum) from ABC where x = ''''||x||'''' and rownum <= 100 UNION ALL' from ABC order by x;
and then tacking a semi-colon on the end will give you the query you asked
for (If I didn't make a silly typo),
but unfortunately I believe the cost of generating the query is likely to
exceed the cost of the other methods proposed.
The "order by x" is gratuitous in anticipation that "x" is of such interest
that there most certainly will be a leading edge
index on it, and thus at least you get the value of an ordered list of your
values of interest from the query.
(wink)
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
Sent: Monday, July 19, 2004 6:06 PM
To: oracle-l_at_freelists.org
Subject: Re: how to write this sql?
select x, least(count(1),100) cnt from ABC group by x;
There is no way to stop counting for a particular x once it reached 100
Regards
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
 |
 |