Home » SQL & PL/SQL » SQL & PL/SQL » Get last 200 records from table in oracle (11g)
Get last 200 records from table in oracle [message #664064] Tue, 04 July 2017 07:23 Go to next message
asliyanage
Messages: 60
Registered: January 2017
Member
There is a table which contain 10 ,000,000 records. I need to get last 200 records that logging after 15 Dec 2015 , 0000 hours.
Login Date is a column in that table which contain logging time. So how to write a query for this.
Re: Get last 200 records from table in oracle [message #664068 is a reply to message #664064] Tue, 04 July 2017 07:33 Go to previous messageGo to next message
BlackSwan
Messages: 26193
Registered: January 2009
Location: SoCal
Senior Member
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

Tables do not have records.
Tables have rows.

Post CREATE TABLE statement for table involved.
Re: Get last 200 records from table in oracle [message #664070 is a reply to message #664068] Tue, 04 July 2017 07:44 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
I don't have table Create statement since this is a question in a paper. Anyway i have a table screenshot line below.

http://i693.photobucket.com/albums/vv299/asliyanage/ABC_1.png?t=1499085835


I think we can use below query. Is there any other way to do this in efficient way ?

select * from users
where user_id >
( (select COUNT(*) from users) - 200)

[Updated on: Tue, 04 July 2017 07:48]

Report message to a moderator

Re: Get last 200 records from table in oracle [message #664072 is a reply to message #664070] Tue, 04 July 2017 07:57 Go to previous messageGo to next message
BlackSwan
Messages: 26193
Registered: January 2009
Location: SoCal
Senior Member
rows in a table are like balls in a basket.
which balls are the last 200 balls?

Posted SQL returns 200 random rows.

post results from SQL below

SELECT MIN(USER_ID), MAX(USER_ID) FROM USERS;
Re: Get last 200 records from table in oracle [message #664074 is a reply to message #664064] Tue, 04 July 2017 08:03 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
Actually this is the real question.Sorry chthe earlier table which is a wrong one.

http://i693.photobucket.com/albums/vv299/asliyanage/ABC_1.png

[Updated on: Tue, 04 July 2017 08:04]

Report message to a moderator

Re: Get last 200 records from table in oracle [message #664075 is a reply to message #664072] Tue, 04 July 2017 08:03 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
select *
  from (
    select rownum as rn, a.*
      from my_table a
      order by order_field_name desc)
  where rn <= 200

table "my_table" should be indexed by "order_field_name"
Re: Get last 200 records from table in oracle [message #664076 is a reply to message #664075] Tue, 04 July 2017 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
table "my_table" should be indexed by "order_field_name"
Why?

Re: Get last 200 records from table in oracle [message #664078 is a reply to message #664075] Tue, 04 July 2017 08:08 Go to previous messageGo to next message
BlackSwan
Messages: 26193
Registered: January 2009
Location: SoCal
Senior Member
bbob wrote on Tue, 04 July 2017 06:03
select *
  from (
    select rownum as rn, a.*
      from my_table a
      order by order_field_name desc)
  where rn <= 200

table "my_table" should be indexed by "order_field_name"
How does above satisfy below?
>I need to get last 200 records that logging after 15 Dec 2015 , 0000 hours.
Re: Get last 200 records from table in oracle [message #664079 is a reply to message #664064] Tue, 04 July 2017 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You still didn't feedback in your previous topics.
You still refuse to comply to forum rules as already asked many times.

Re: Get last 200 records from table in oracle [message #664080 is a reply to message #664075] Tue, 04 July 2017 08:10 Go to previous messageGo to next message
cookiemonster
Messages: 13285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Rownum is worked out before the order by is applied, so that won't work. If you used row_number instead it would, but there's a simpler solution:
select *
  from (
    select a.*
      from my_table a
      order by order_field_name desc)
  where rownum <= 200
Re: Get last 200 records from table in oracle [message #664081 is a reply to message #664080] Tue, 04 July 2017 08:11 Go to previous messageGo to next message
cookiemonster
Messages: 13285
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll need to add a where clause to answer the actual question.
Re: Get last 200 records from table in oracle [message #664083 is a reply to message #664078] Tue, 04 July 2017 08:17 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
Just add the condition:

select *
  from (
    select a.*
      from SUPPLIER a
      where LAST_LOGIN_DATE >= to_date('2015-12-15', 'YYYY-MM-DD')
      order by LAST_LOGIN_DATE desc)
  where rownum <= 200

I thought you could do it yourself
Re: Get last 200 records from table in oracle [message #664086 is a reply to message #664080] Tue, 04 July 2017 08:19 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
yes, you're right
Re: Get last 200 records from table in oracle [message #664155 is a reply to message #664086] Wed, 05 July 2017 12:23 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
Thanks all, bbob's reply is seems correct
Re: Get last 200 records from table in oracle [message #664160 is a reply to message #664155] Wed, 05 July 2017 13:46 Go to previous messageGo to next message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
JUST FOR FUN, USING THE ROW_NUMBER ANALYTIC

select *
from (select a.*,ROW_NUMBER() OVER (order by last_login_date desc) rn
      from SUPPLIER a
      where a.LAST_LOGIN_DATE >= to_date('2015-12-15', 'YYYY-MM-DD'))
and rn <= 200;

[Updated on: Thu, 06 July 2017 06:02]

Report message to a moderator

Re: Get last 200 records from table in oracle [message #664162 is a reply to message #664160] Wed, 05 July 2017 15:09 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
JUST FOR FUN, USING THE ROW_NUMBER ANALYTIC
Which is the correct way to do it in the current versions.

Re: Get last 200 records from table in oracle [message #664222 is a reply to message #664162] Fri, 07 July 2017 03:18 Go to previous messageGo to next message
cookiemonster
Messages: 13285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Any reason why it's more correct than rownum?
Re: Get last 200 records from table in oracle [message #664223 is a reply to message #664222] Fri, 07 July 2017 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know the reason, just Tom Kyte gave me this answer years ago: "if you really want to use rownum :)".
I also noticed that Oracle uses this function behind the scene for the new SQL features like TOP so this leads me to the conclusion they will investigate in this function improvements and let rownum aside (just like LONG vs LOB).
Without speaking about the far more things you can do with an analytical function you can't with rownum like getting the last 10 rows per group so no reason to use rownum for the single case it works and row_number for all the other ones above all when you read the artificial construction you have to do to make it works (ordering an inline view).

[Updated on: Fri, 07 July 2017 03:28]

Report message to a moderator

Re: Get last 200 records from table in oracle [message #664230 is a reply to message #664223] Fri, 07 July 2017 10:16 Go to previous messageGo to next message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
rownum is very restricted. For example the following will never work

where rownum between 10 and 20

however using the row_number analytic and assigning the alias rn (for example)

where rn between 10 and 20

is totally legal and very fast
Re: Get last 200 records from table in oracle [message #664231 is a reply to message #664230] Fri, 07 July 2017 11:15 Go to previous message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes and this what Oracle uses in 12c:
SCOTT> select * from emp order by empno;
    EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 23/05/1987 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10

14 rows selected.

SCOTT> select * from emp order by empno offset 5 rows fetch next 5 rows only;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500          0         30

5 rows selected.
And the EXPLAIN PLAN gives:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3291446077

---------------------------------------------------------------
| Id  | Operation                | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     14 |     4  (25)|
|*  1 |  VIEW                    |      |     14 |     4  (25)|
|*  2 |   WINDOW SORT PUSHED RANK|      |     14 |     4  (25)|
|   3 |    TABLE ACCESS FULL     | EMP  |     14 |     3   (0)|
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN
              (5>=0) THEN 5 ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownu
              mber">5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."EMPNO")<=CASE  WHEN
              (5>=0) THEN 5 ELSE 0 END +5)
Previous Topic: Regular Expressions
Next Topic: Appending CLOB
Goto Forum:
  


Current Time: Thu Oct 18 16:58:43 CDT 2018