Home » SQL & PL/SQL » SQL & PL/SQL » Retrun a limit set of rows (9i or 11g)

Re: Retrun a limit set of rows [message #674786 is a reply to message #674783] 
Sun, 17 February 2019 16:40 

BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal

Senior Member 


Welcome to this forum
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Avoid attached files; just post inline
1,a
1,a
2,a
2,a
3,a
4,a
5,a
5,a
5,b
5,b
5,b
5,a
6,a
6,b
7,b
9,a
9,a
9,a
9,a
9,a
BTW, rows in a table are like balls in a basket. They have no order. Which is first ball in a basket?





Re: Retrun a limit set of rows [message #674800 is a reply to message #674783] 
Mon, 18 February 2019 03:01 

Michel Cadot
Messages: 68055 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


Here's an example of what you can do (some columns are useless and only there to show you how it works):
SQL> break on grp dup skip 1
SQL> with
2 data as (
3 select grp, val, row_number() over (order by grp, val) rn,
4 decode(lag(grp,1,0) over (order by grp, val),
5 grp, to_number(null),
6 row_number() over (order by grp, val)) rn2
7 from t
8 )
9 select grp, val, rn,
10 last_value(rn2 ignore nulls)
11 over (order by grp, val
12 rows between unbounded preceding and current row)
13 first_rn,
14 nvl(first_value(rn2 ignore nulls)
15 over (order by grp
16 rows between 1 following and unbounded following)1,
17 count(*) over())
18 last_rn
19 from data
20 /
GRP VAL RN FIRST_RN LAST_RN
    
1 a 1 1 2
1 a 2 1 2
2 a 3 3 4
2 a 4 3 4
3 a 5 5 5
4 a 6 6 6
5 a 7 7 12
5 a 8 7 12
5 a 9 7 12
5 b 10 7 12
5 b 11 7 12
5 b 12 7 12
6 a 13 13 14
6 b 14 13 14
7 b 15 15 15
9 a 16 16 20
9 a 17 16 20
9 a 18 16 20
9 a 19 16 20
9 a 20 16 20
20 rows selected.
Note that this won't work in 9i as "ignore nulls" were introduced in 10g as far as I remember.





Re: Retrun a limit set of rows [message #674829 is a reply to message #674783] 
Mon, 18 February 2019 18:15 
Solomon Yakobson
Messages: 3106 Registered: January 2010 Location: Connecticut, USA

Senior Member 


SQL> with r(
2 a,
3 b,
4 cnt,
5 desired_cnt,
6 rn
7 ) as (
8 select a,
9 b,
10 count(*) over() cnt,
11 &desired_count,
12 rownum rn
13 from tbl
14 where a = 1
15 union all
16 select t.a,
17 t.b,
18 r.cnt + count(*) over() cnt,
19 r.desired_cnt,
20 rownum rn
21 from tbl t,
22 r
23 where t.a = r.a + 1
24 and r.cnt <= r.desired_cnt
25 and r.rn = 1
26 )
27 select a,
28 b
29 from r
30 where cnt <= desired_cnt
31 /
Enter value for desired_count: 10
A B
 
1 a
1 a
2 a
2 a
3 a
4 a
6 rows selected.
SQL> /
Enter value for desired_count: 15
A B
 
1 a
1 a
2 a
2 a
3 a
4 a
5 a
5 a
5 b
5 b
5 b
5 a
6 a
6 b
7 b
15 rows selected.
SQL>
SY.




Re: Retrun a limit set of rows [message #674840 is a reply to message #674830] 
Tue, 19 February 2019 04:20 

Michel Cadot
Messages: 68055 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


Quote:however I may have made the example a little to simple.
This is why I gave the base of something that can be generalized.
For your specific example:
SQL> with
2 data as (
3 select grp, val, row_number() over (order by grp, val) rn,
4 decode(lag(grp,1,0) over (order by grp, val),
5 grp, to_number(null),
6 row_number() over (order by grp, val)) rn2
7 from t
8 ),
9 tagging as (
10 select grp, val, rn,
11 last_value(rn2 ignore nulls)
12 over (order by grp, val
13 rows between unbounded preceding and current row)
14 first_rn,
15 nvl(first_value(rn2 ignore nulls)
16 over (order by grp
17 rows between 1 following and unbounded following)1,
18 count(*) over())
19 last_rn
20 from data
21 )
22 select grp, val
23 from tagging
24 where last_rn <= &desired_count
25 order by grp, val
26 /
Enter value for desired_count: 10
GRP VAL
 
1 a
1 a
2 a
2 a
3 a
4 a
6 rows selected.
SQL> /
Enter value for desired_count: 15
GRP VAL
 
1 a
1 a
2 a
2 a
3 a
4 a
5 a
5 a
5 a
5 b
5 b
5 b
6 a
6 b
7 b
15 rows selected.



Re: Retrun a limit set of rows [message #674846 is a reply to message #674830] 
Tue, 19 February 2019 07:09 
Solomon Yakobson
Messages: 3106 Registered: January 2010 Location: Connecticut, USA

Senior Member 


Not an issue. All you need is column a ranking:
SQL> update tbl set a = a * 11
2 where a > 2
3 /
16 rows updated.
SQL> select *
2 from tbl
3 /
A B
 
1 a
1 a
2 a
2 a
33 a
44 a
55 a
55 a
55 b
55 b
55 b
A B
 
55 a
66 a
66 b
77 b
99 a
99 a
99 a
99 a
99 a
20 rows selected.
SQL> with t as (
2 select a,
3 b,
4 dense_rank() over(order by a) a_rnk
5 from tbl
6 ),
7 r(
8 a,
9 b,
10 a_rnk,
11 cnt,
12 desired_cnt,
13 rn
14 ) as (
15 select a,
16 b,
17 a_rnk,
18 count(*) over() cnt,
19 &desired_count,
20 rownum rn
21 from t
22 where a_rnk = 1
23 union all
24 select t.a,
25 t.b,
26 t.a_rnk,
27 r.cnt + count(*) over() cnt,
28 r.desired_cnt,
29 rownum rn
30 from t,
31 r
32 where t.a_rnk = r.a_rnk + 1
33 and r.cnt <= r.desired_cnt
34 and r.rn = 1
35 )
36 select a,
37 b
38 from r
39 where cnt <= desired_cnt
40 /
Enter value for desired_count: 10
old 19: &desired_count,
new 19: 10,
A B
 
1 a
1 a
2 a
2 a
33 a
44 a
6 rows selected.
SQL> /
Enter value for desired_count: 15
old 19: &desired_count,
new 19: 15,
A B
 
1 a
1 a
2 a
2 a
33 a
44 a
55 b
55 a
55 a
55 b
55 a
A B
 
55 b
66 a
66 b
77 b
15 rows selected.
SQL>
And keep in mind, recursive solution I posted stops as soon as cumulative count exceeds desired count while Michel's solution calculates all cumulative counts and only then filters out rows where it exceed desired count.
SY.






Re: Retrun a limit set of rows [message #674862 is a reply to message #674860] 
Tue, 19 February 2019 15:53 
Solomon Yakobson
Messages: 3106 Registered: January 2010 Location: Connecticut, USA

Senior Member 


Match recognize (assuming you are on 12C) is, IMHO, simplest and most efficient solution:
SQL> select *
2 from tbl
3 /
A B
 
1 a
1 a
2 a
2 a
33 a
44 a
55 a
55 a
55 b
55 b
55 b
A B
 
55 a
66 a
66 b
77 b
99 a
99 a
99 a
99 a
99 a
20 rows selected.
SQL> with t as (
2 select *
3 from tbl
4 match_recognize(
5 order by a,b
6 measures final last(a) as last_a
7 all rows per match
8 pattern(x{&desired_count} y)
9 define x as 1 = 1
10 )
11 )
12 select a,
13 b
14 from t
15 where a != last_a
16 /
Enter value for desired_count: 10
old 8: pattern(x{&desired_count} y)
new 8: pattern(x{10} y)
A B
 
1 a
1 a
2 a
2 a
33 a
44 a
6 rows selected.
SQL> /
Enter value for desired_count: 15
old 8: pattern(x{&desired_count} y)
new 8: pattern(x{15} y)
A B
 
1 a
1 a
2 a
2 a
33 a
44 a
55 a
55 a
55 a
55 b
55 b
A B
 
55 b
66 a
66 b
77 b
15 rows selected.
SQL>
SY.



Goto Forum:
Current Time: Wed Dec 08 10:45:08 CST 2021
