Home » SQL & PL/SQL » SQL & PL/SQL » Retrun a limit set of rows (9i or 11g)
Retrun a limit set of rows [message #674783] Sun, 17 February 2019 14:32 Go to next message
torz
Messages: 7
Registered: February 2019
Junior Member
Hi All,

I'm going to try and explain what I'm trying to do to the best of my ability.
I haven't googled because I honestly have no idea what to start googling for Confused

So, I have a table, in this case with 2 columns (see attached excel sheet).
What I need to do is specify the number of rows to return which is easy obviously, but now it must only return <= the number of rows specified where the value in column a is the same.

So using the excel sheet as an example.
I want to return 10 rows - what I would expect to be returned is rows 1 to 6 because the number 5 continues past the 10th row.

If I wanted 15 rows returned it would return rows 1 to 15 because the number 7 does not go past the specified number of rows.


I really hope I've made sense Laughing

Any help would be greatly appreciated, SQL, PLSQL whatever the best way to handle it is...
  • Attachment: Book1.csv
    (Size: 0.08KB, Downloaded 264 times)
Re: Retrun a limit set of rows [message #674786 is a reply to message #674783] Sun, 17 February 2019 16:40 Go to previous messageGo to next message
BlackSwan
Messages: 26648
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 #674787 is a reply to message #674786] Sun, 17 February 2019 17:45 Go to previous messageGo to next message
torz
Messages: 7
Registered: February 2019
Junior Member
column A would be ordered so the same number is grouped together.

It don't believe it would matter which number was actually first, you could order asc or desc & expect the same outcome.


If want to return 10 rows - what I would expect to be returned is rows 1 to 6 because the number 5 continues past the 10th row.
If I wanted 15 rows returned it would return rows 1 to 15 because the number 7 does not go past the specified number of rows.

does that help explain what I'm trying to do ?
Re: Retrun a limit set of rows [message #674793 is a reply to message #674787] Mon, 18 February 2019 01:46 Go to previous messageGo to next message
John Watson
Messages: 8120
Registered: January 2010
Location: Global Village
Senior Member
What SQL have you tried so far?
Re: Retrun a limit set of rows [message #674800 is a reply to message #674783] Mon, 18 February 2019 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 66776
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 #674823 is a reply to message #674800] Mon, 18 February 2019 14:48 Go to previous messageGo to next message
torz
Messages: 7
Registered: February 2019
Junior Member
Thanks Michel,

This is very interesting!

It looks like this might work for me, thank you so much - had no idea where to start or even what to try and google to work it out Smile
Re: Retrun a limit set of rows [message #674828 is a reply to message #674823] Mon, 18 February 2019 15:53 Go to previous messageGo to next message
torz
Messages: 7
Registered: February 2019
Junior Member
and yes you are correct - does not work in 9i, which is fine can use our 11g instance instead.
Re: Retrun a limit set of rows [message #674829 is a reply to message #674783] Mon, 18 February 2019 18:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
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 #674830 is a reply to message #674829] Mon, 18 February 2019 18:33 Go to previous messageGo to next message
torz
Messages: 7
Registered: February 2019
Junior Member
Thanks Sy,

This looks exactly like what I had envisioned, however I may have made the example a little to simple.
Because reading this it does not look like it would work for the real live data where column a could be 20001475175 then 20001756235 then 2001432115794, it is never going to actually be sequential.

Could this be adapted to suit somehow ?
Re: Retrun a limit set of rows [message #674840 is a reply to message #674830] Tue, 19 February 2019 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 66776
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
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 #674850 is a reply to message #674846] Tue, 19 February 2019 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 66776
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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.

Only true if you keep the general form I gave (to be able to get other rows than the first page).
If you always want the first page of (at most) "desired_count" rows you can limit the source in the first subquery adding in the second one "where rn <= &desired_count".

Re: Retrun a limit set of rows [message #674860 is a reply to message #674840] Tue, 19 February 2019 14:51 Go to previous messageGo to next message
torz
Messages: 7
Registered: February 2019
Junior Member
Thanks Michel,

Being rather new to the whole sql thing, I don't think I would have got to the 2nd example - it works perfectly Smile
Re: Retrun a limit set of rows [message #674861 is a reply to message #674846] Tue, 19 February 2019 15:39 Go to previous messageGo to next message
torz
Messages: 7
Registered: February 2019
Junior Member
Thanks Sy,

The 2nd of yours works fantastic as well Smile
Re: Retrun a limit set of rows [message #674862 is a reply to message #674860] Tue, 19 February 2019 15:53 Go to previous message
Solomon Yakobson
Messages: 2908
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.
Previous Topic: Scrambling the data for dev environment after refresh to hide/mask the sensitive data
Next Topic: Connect by clause taking lot of time
Goto Forum:
  


Current Time: Sat Jan 18 23:49:16 CST 2020