Getting Last 25 Rows of a Table 1

[jtanner]

Hello,

I need to retreive the last 25 rows of a log table that does not have an index and do it using one SQL command.

I can get the first 25 using this:

SELECT text FROM scott.alert_log WHERE rownum < 25

I tried getting the last 25 using this SELECT text FROM scott.alert_log WHERE rownum > max(rownum)-25; but get the error:

SELECT text FROM scott.alert_log WHERE rownum > max(rownum)-25
                                                 *
ERROR at line 1:
ORA-00934: group function is not allowed here

What can you recommend?

Thanks,

JT

 

[SantaMufasa]

JT,

First of all, Oracle doesn't store rows in any particular order in a table. So, when you say "Getting Last 25 Rows of a Table", are you interested in:

1) the last physical 25 (possibly randomly ordered) rows (which probably mean nothing from a business perspective), or

2) the last logical 25 rows, based upon some order that you specify?

In either case, there is code, below, that gives gives you any number of (first or last) rows from any table you want. First, let's post some sample data from a table:

select last_name, salary from s_emp;

LAST_NAME                     SALARY
------------------------- ----------
Velasquez                       2500
Ngao                            1450
Nagayama                        1400
Quick-To-See                    1450
Ropeburn                        1550
Urguhart                        1200
Menchu                          1250
Biri                            1100
Catchpole                       1300
Havel                           1307
Magee                           1400
Giljum                          1490
Sedeghi                         1515
Nguyen                          1525
Dumas                           1450
Maduro                          1400
Smith                            940
Nozaki                          1200
Patel                            795
Newman                           750
Markarian                        850
Chang                            800
Patel                            795
Dancs                            860
Schwartz                        1100

25 rows selected.

Let's now run my script, "LastNRows.sql", to show some behaviours of this script:

SQL> @LastNRows
How many of the 'last' rows of a table do you wish to see?: 4

Enter the (possibly qualified) name of the table
    from which you wish to extract the last 4 rows: test.s_emp

Enter the expressions you wish to SELECT/display: salary, last_name

Complete this clause: 'Order by ...': salary
[i](Note: order by "salary" gives lowest paid 4 employees)[/i]


    SALARY LAST_NAME
---------- -------------------------
       750 Newman
       795 Patel
       795 Patel
       800 Chang

SQL> @LastNRows
How many of the 'last' rows of a table do you wish to see?: 3

Enter the (possibly qualified) name of the table
    from which you wish to extract the last 3 rows: s_emp

Enter the expressions you wish to SELECT/display: salary, last_name

Complete this clause: 'Order by ...': salary desc
[i](Note: order by "salary [b]desc[/b]" gives highest paid 3 employees)[/i]


    SALARY LAST_NAME
---------- -------------------------
      2500 Velasquez
      1550 Ropeburn
      1525 Nguyen

SQL> @LastNRows
How many of the 'last' rows of a table do you wish to see?: 3

Enter the (possibly qualified) name of the table
    from which you wish to extract the last 3 rows: s_emp

Enter the expressions you wish to SELECT/display: last_name

Complete this clause: 'Order by ...': rownum
[i](Note: order by "rownum" gives first 3 physical rows of table)[/i]


LAST_NAME
-------------------------
Velasquez
Ngao
Nagayama

SQL> @LastNRows
How many of the 'last' rows of a table do you wish to see?: 3

Enter the (possibly qualified) name of the table
    from which you wish to extract the last 3 rows: s_emp

Enter the expressions you wish to SELECT/display: last_name

Complete this clause: 'Order by ...': rownum desc
[i](Note: order by "rownum [b]desc[/b]" gives last 3 physical rows of table)[/i]


LAST_NAME
-------------------------
Schwartz
Dancs
Patel

Now, here is the code for the "LastNRows.sql" script:

accept cnt prompt "How many of the 'last' rows of a table do you wish to see?: "
prompt
prompt Enter the (possibly qualified) name of the table
accept tabname prompt "    from which you wish to extract the last &cnt rows: "
prompt
accept Selects prompt "Enter the expressions you wish to SELECT/display: "
prompt
accept orderby prompt "Complete this clause: 'Order by ...': "
prompt
select &selects
  from (select rownum rn, x.*
          from (select * from &tabname
                 order by &orderby) x)
 where rn <= &cnt;

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[engineer2100]

Santa, (please dont take it other way!!)

I strongly feel the experience you have, you should concentrate on much larger, complex critical issues than solving this small things!!

Such small things should be left to such, less experienced folks like us!!!

-Engi

 

[jtanner]

Santa, Engineer2100,

Thanks for the response. I agree with Engineer2100. If this was a basic question you are right on.

You know the answers you get here or anywhere are only as good as the question. I am embarrassed to say I left out a vital piece of info. My table is an External table and using an index is not possible.

My external table is the Oracle alert log via the help I received here. My goal is to display the last n (say 25) entries in this log\External Table.

What can you recommend?

JT

 

[SantaMufasa]

What can you recommend?

I recommend that you use the script...It works fine with external tables.

Here is the proof-of-concept using my Oracle alert log, showing the last 25 lines from the alert log:

SQL> set verify off
SQL> @lastnrows
How many of the 'last' rows of a table do you wish to see?: 25

Enter the (possibly qualified) name of the table
    from which you wish to extract the last 25 rows: ALERT_LOG_TABLE

Enter the expressions you wish to SELECT/display: *

Complete this clause: 'Order by ...': rownum desc

RN MSG
-- ---------------------------------------------------------------
 1 ORA-03113: end-of-file on communication channel
 2 ORA-02068: following severe error from RIV
 3 ORA-28546: connection initialization failed
 4 Errors in file c:\oracle\admin\dhunt\bdump\dhunt_reco_2620.trc:
 5 Thu Jul 05 07:53:58 2007
 6 ORA-03113: end-of-file on communication channel
 7 ORA-02068: following severe error from RIV
 8 ORA-28546: connection initialization failed
 9 Errors in file c:\oracle\admin\dhunt\bdump\dhunt_reco_2620.trc:
10 Thu Jul 05 07:33:13 2007
11 ORA-03113: end-of-file on communication channel
12 ORA-02068: following severe error from RIV
13 ORA-28546: connection initialization failed
14 Errors in file c:\oracle\admin\dhunt\bdump\dhunt_reco_2620.trc:
15 Thu Jul 05 06:52:55 2007
16 ORA-03113: end-of-file on communication channel
17 ORA-02068: following severe error from RIV
18 ORA-28546: connection initialization failed
19 Errors in file c:\oracle\admin\dhunt\bdump\dhunt_reco_2620.trc:
20 Thu Jul 05 06:15:17 2007
21 ORA-03113: end-of-file on communication channel
22 ORA-02068: following severe error from RIV
23 ORA-28546: connection initialization failed
24 Errors in file c:\oracle\admin\dhunt\bdump\dhunt_reco_2620.trc:
25 Thu Jul 05 04:58:07 2007

25 rows selected.
******************************************************************

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jtanner]

Santa,

Your script worked perfect.

Thanks very much for your advice and posting,

JT

 

[engineer2100]

Santa is the best !!!! to Santa