Re: how to write this sql?
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 19 Jul 2004 22:22:00 +0200
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
529 consistent gets
528 physical reads
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$)
5 where cnt <= 50
6 group by user#
7 /
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
529 consistent gets
528 physical reads
0 redo size
999 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
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$)
6 group by user#
7 /
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
529 consistent gets
528 physical reads
0 redo size
999 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
>
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
