OraFAQ Forum: SQL & PL/SQL » How to fetch last N records from the table?

Home » SQL & PL/SQL » SQL & PL/SQL » How to fetch last N records from the table?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to fetch last N records from the table? [message #611934]

Thu, 10 April 2014 05:04

Bikash.p
Messages: 11
Registered: April 2014
Location: Banglore

Junior Member

Suppose in a table 6 record is there.And I want to fetch last 2 records. What is the query?
create table student2(sid int,sname char(10),email varchar(15),fee number(10),dob date,city varchar(10));
insert into student2 values(1,'aaa','bk1@',200,'24-jan-1991','kkp1');
insert into student2 values(2,'bbb','bk2@',300,'04-feb-1981','kkp6');
insert into student2 values(3,'ccc','bk3@',400,'21-mar-1989','kkp5');
insert into student2 values(4,'ddd','bk4@',300,'28-apr-1992','kkp4');
insert into student2 values(5,'eee','bk5@',200,'29-may-1993','kkp3');
insert into student2 values(6,'fff','bk6@',700,'16-jun-1998','kkp2');
commit;

Report message to a moderator

Re: How to fetch last N records from the table? [message #611936 is a reply to message #611934]

Thu, 10 April 2014 05:09

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

You need something to order by - there's no concept of a "last" record in a table naturally.

You have to have a field to order by then do something like this:

select * from (
select * from TABLE order by YOUR_FIELD desc)
where rownum <=2

[Updated on: Thu, 10 April 2014 05:09]

Report message to a moderator

Re: How to fetch last N records from the table? [message #611937 is a reply to message #611934]

Thu, 10 April 2014 05:09

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

Is this a college homework question? If so, you need to show the SQL you have tried so far. And by the way, you need to use TO_DATE in your INSERT statements:

orclz> create table student2(sid int,sname char(10),email varchar(15),fee number(10),dob date,city varchar(10));

Table created.

orclz> insert into student2 values(1,'aaa','bk1@',200,'24-jan-1991','kkp1');
insert into student2 values(1,'aaa','bk1@',200,'24-jan-1991','kkp1')
                                               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


orclz>

Report message to a moderator

Re: How to fetch last N records from the table? [message #611946 is a reply to message #611937]

Thu, 10 April 2014 05:54

Bikash.p
Messages: 11
Registered: April 2014
Location: Banglore

Junior Member

Yes John ,

You are rite. Its not a school task. Actually i was trying myself. The example is for the Understanding purpose. Please Ignore that Error. IN sql devloper it will work. Anyways can you give me the solution?

Regards

Report message to a moderator

Re: How to fetch last N records from the table? [message #611947 is a reply to message #611946]

Thu, 10 April 2014 06:06

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Bikash Patra wrote on Thu, 10 April 2014 11:54

Please Ignore that Error. IN sql devloper it will work.

In your sql developer it might work, but that doesn't mean it's going to work in anyone elses, since it depends on default date formats which are language/region specific and can be changed. If you're learning you should learn to always to_date dates and include a format mask.
Read this http://edstevensdba.wordpress.com/category/nls_date_format/

Bikash Patra wrote on Thu, 10 April 2014 11:54

Anyways can you give me the solution?

Roachcoach already did.

Report message to a moderator

Re: How to fetch last N records from the table? [message #611948 is a reply to message #611947]

Thu, 10 April 2014 06:39

Bikash.p
Messages: 11
Registered: April 2014
Location: Banglore

Junior Member

Thanks A ton Robert Cooke & Roachcoach .

If i do not use order by and desc is there any other way to find the solution?

Regards

Report message to a moderator

Re: How to fetch last N records from the table? [message #611949 is a reply to message #611948]

Thu, 10 April 2014 06:59

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Bikash Patra wrote on Thu, 10 April 2014 06:39

Thanks A ton Robert Cooke & Roachcoach .

If i do not use order by and desc is there any other way to find the solution?

Regards

Without ORDER BY there is not even a concept of a solution.

Rows in a table are like tennis balls in a basket. There is no "first" or "last". There is not even a concept of "first" or "last".

Report message to a moderator

Re: How to fetch last N records from the table? [message #611954 is a reply to message #611934]

Thu, 10 April 2014 07:20

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

You can add a column with "INSERT"-date.

for example

CREATE TABLE TEST
(
  APPLICATIONNAME  VARCHAR2(120 BYTE),
  PRODUCTNAME      VARCHAR2(70 BYTE),
  HOSTNAME         VARCHAR2(60 BYTE),
  VIRTUALHOST      VARCHAR2(60 BYTE),
  HARDWARENAME     VARCHAR2(60 BYTE),
  HWMODEL          VARCHAR2(60 BYTE),
  STANDORT         VARCHAR2(60 BYTE),
  STATUS           VARCHAR2(60 BYTE),
  OS               VARCHAR2(60 BYTE),
  OSVERSION        VARCHAR2(60 BYTE),
  IMPORTED         DATE                         DEFAULT sysdate
)

see

IMPORTED         DATE                         DEFAULT sysdate

Then you can order with insert date column and limit output with rownum.

Report message to a moderator

Re: How to fetch last N records from the table? [message #611955 is a reply to message #611954]

Thu, 10 April 2014 07:25

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Such as this?

SQL> insert into test (id)
  2  select level from dual connect by level <= 10;

10 rows created.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select * from test;

        ID IMPORTED
---------- -------------------
         1 10.04.2014 14:24:06
         2 10.04.2014 14:24:06
         3 10.04.2014 14:24:06
         4 10.04.2014 14:24:06
         5 10.04.2014 14:24:06
         6 10.04.2014 14:24:06
         7 10.04.2014 14:24:06
         8 10.04.2014 14:24:06
         9 10.04.2014 14:24:06
        10 10.04.2014 14:24:06

10 rows selected.

SQL>

How can I order these values by insert date?

Report message to a moderator

Re: How to fetch last N records from the table? [message #611956 is a reply to message #611955]

Thu, 10 April 2014 07:43

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

Littlefoot wrote on Thu, 10 April 2014 07:25

Such as this?
How can I order these values by insert date?

for your use case SYSTIMESTAMP is better.

SELECT SYSTIMESTAMP FROM DUAL

[Updated on: Thu, 10 April 2014 07:45]

Report message to a moderator

Re: How to fetch last N records from the table? [message #611958 is a reply to message #611956]

Thu, 10 April 2014 07:54

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This?

SQL> create table test (id number, imported timestamp default systimestamp);

Table created.

SQL> insert into test (id) select level from dual connect by level <= 10;

10 rows created.

SQL> col l_imp format a30
SQL> select id, to_char(imported, 'dd.mm.yyyy hh24:mi:ss ff9') l_imp from test;

        ID L_IMP
---------- ------------------------------
         1 10.04.2014 14:53:40 064000000
         2 10.04.2014 14:53:40 064000000
         3 10.04.2014 14:53:40 064000000
         4 10.04.2014 14:53:40 064000000
         5 10.04.2014 14:53:40 064000000
         6 10.04.2014 14:53:40 064000000
         7 10.04.2014 14:53:40 064000000
         8 10.04.2014 14:53:40 064000000
         9 10.04.2014 14:53:40 064000000
        10 10.04.2014 14:53:40 064000000

10 rows selected.

SQL>

Report message to a moderator

Re: How to fetch last N records from the table? [message #611961 is a reply to message #611955]

Thu, 10 April 2014 07:57

VladGab
Messages: 11
Registered: March 2014

Junior Member

first create sequence.

create sequence SEQ_LOG
minvalue 1
maxvalue 999999999999999
increment by 1
nocache;

then create your table which contain the column of sequence

CREATE TABLE sutdent1
(
    /*your columns*/
    SEQID integer NOT NULL default SEQ_LOG.nextval
);

after this you can query last two rows. For example

select * 
from student1,dual
where to_char(SEQID) = dummy(+) 
and rownum <= 2
order by SEQID desc

Report message to a moderator

Re: How to fetch last N records from the table? [message #611962 is a reply to message #611958]

Thu, 10 April 2014 07:59

rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg

Senior Member

@Littlefoot
you wish to test me?

You need a timestamp data type (column imported)

But you know much better than me..

@VladGab
thanks. Thats the solution.

[Updated on: Thu, 10 April 2014 08:01]

Report message to a moderator

Re: How to fetch last N records from the table? [message #611963 is a reply to message #611956]

Thu, 10 April 2014 08:00

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

Senior Member
Account Moderator

Not more:

SQL> insert into test (id) select level from dual connect by level <= 10;

10 rows created.

SQL>  select * from test;
        ID IMPORTED
---------- ---------------------------------------------------------------------
         1 10/04/2014 14:57:04.390
         2 10/04/2014 14:57:04.390
         3 10/04/2014 14:57:04.390
         4 10/04/2014 14:57:04.390
         5 10/04/2014 14:57:04.390
         6 10/04/2014 14:57:04.390
         7 10/04/2014 14:57:04.390
         8 10/04/2014 14:57:04.390
         9 10/04/2014 14:57:04.390
        10 10/04/2014 14:57:04.390

10 rows selected.

SQL> truncate table test;

Table truncated.

SQL>  insert into test (id,imported) select level, systimestamp from dual connect by level <= 10;

10 rows created.

SQL> select * from test;
        ID IMPORTED
---------- ---------------------------------------------------------------------------
         1 10/04/2014 14:58:20.500
         2 10/04/2014 14:58:20.500
         3 10/04/2014 14:58:20.500
         4 10/04/2014 14:58:20.500
         5 10/04/2014 14:58:20.500
         6 10/04/2014 14:58:20.500
         7 10/04/2014 14:58:20.500
         8 10/04/2014 14:58:20.500
         9 10/04/2014 14:58:20.500
        10 10/04/2014 14:58:20.500

10 rows selected.

Report message to a moderator

Re: How to fetch last N records from the table? [message #611964 is a reply to message #611962]

Thu, 10 April 2014 08:01

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

Senior Member
Account Moderator

rc3d wrote on Thu, 10 April 2014 14:59

@Littlefoot
you wish to test me?

You need a timestamp data type (column imported)

But you know much better than me..

@VladGab
thanks. Thats the solution.

Type is irrelevant, date/time expression is evaluated only once per statement.

[Updated on: Thu, 10 April 2014 08:02]

Report message to a moderator

Previous Topic:	"Where 1=2 " . How it works internally?
Next Topic:	Generating PDF files at oracle 10g server

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 19:51:32 CDT 2024