| 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?
Guang,
I would try to use the row_number() analytical function in a subquery, but I am not convinced by the result :
SQL> @a
SQL> select user#, least(count(1), 50)
2 from sys.seg$
3 group by user#
4 /
USER# LEAST(COUNT(1),50)
---------- ------------------
0 50
5 50
11 5
18 19
19 11
21 2
22 50
24 50
26 19
27 19
46 3
47 2
50 46
62 9
14 rows selected.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'SEG$'
Statistics
0 recursive calls
5 db block gets
0 redo size
1009 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> @a2
SQL> select user#, max(cnt)
2 from (select user#, row_number() over (partition by user#
3 order by 1) cnt 4 from sys.seg$)
USER# MAX(CNT)
---------- ----------
0 50
5 50
11 5
18 19
19 11
21 2
22 50
24 50
26 19
27 19
46 3
47 2
50 46
62 9
14 rows selected.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 VIEW
3 2 WINDOW (SORT)
4 3 TABLE ACCESS (FULL) OF 'SEG$'
Statistics
0 recursive calls
5 db block gets
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> @a3
SQL> select user#, max(cnt)
2 from (select user#, count(1) over (partition by user#
3 order by 1 4 rows between 0 preceding and 49 following) cnt 5 from sys.seg$)
USER# MAX(CNT)
---------- ----------
0 50
5 50
11 5
18 19
19 11
21 2
22 50
24 50
26 19
27 19
46 3
47 2
50 46
62 9
14 rows selected.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 VIEW
3 2 WINDOW (SORT)
4 3 TABLE ACCESS (FULL) OF 'SEG$'
Statistics
0 recursive calls
5 db block gets
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
Guang Mei wrote:
>Oracle 8173.
>
>I have a query like this:
>
>select x, count(1) cnt from ABC group by x;
>
>which shows:
>
> X CNT
>---------- ----------
> 1 25
> 2 18
> 6 156
> 7 529
> 8 43
> 9 355
>
>
>What I want is
>
> X CNT
>---------- ----------
> 1 25
> 2 18
> 6 100
> 7 100
> 8 43
> 9 100
>
>This means any count > 100 will be replaced with value 100. I have no
>problem of displaying this. What I would like to have is to let oracle know
>that when the count reaches 100, stop counting for that x. This way oracle
>does not need to keep scan after cnt reaches 100, hopefully saving some
>time. (somehow it is similar to adding rownum=1 to let oracle stop when
>certain condition is met).
>
>Is it possible to add something to the query to accomplish this?
>
>TIA.
>
>Guang
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Jul 19 2004 - 15:19:53 CDT
 |
 |