Home » SQL & PL/SQL » SQL & PL/SQL » select the last 4 rows of data from a table
select the last 4 rows of data from a table [message #219929] Fri, 16 February 2007 15:02 Go to next message
bztom33
Messages: 95
Registered: June 2005
Member
Hi,

Is there a way to select the last 4 rows of data from a table with a given id_number?

A set of data is assoicated with a id_number, but the data can be more than 6 months to a year old on a given id_number. I have not been able to figure out how to get the last 4 rows of data.

I failed to generate any results when I try to use date_time between a given date time.


My table structure is:

create table testa(
id_number number(5),
date_time date,
value number,
flag char(1),
constraint testa_pk primary key(fcst_sensor_id,date_time);

example data:

...older data ...

insert into testa(id_number,date_time,value,flag)
values(42,'02/17/2005 12:00');

insert into testa(id_number,date_time,value,flag)
values(42,'02/17/2006 12:00');

insert into testa(id_number,date_time,value,flag)
values(42,'04/17/2006 12:00');

insert into testa(id_number,date_time,value,flag)
values(42,'02/16/2007 12:00');


I want to retrieve the last four entries from the table regardless of the date_time.



Thanks

Tom

[Updated on: Fri, 16 February 2007 15:03]

Report message to a moderator

Re: select the last 4 rows of data from a table [message #219930 is a reply to message #219929] Fri, 16 February 2007 15:07 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>I want to retrieve the last four entries from the table regardless of the date_time.
#1 this is a FAQ.
#2 in any RDBMS there is no such concept of FIRST or LAST without the use of ORDER BY clause.
Re: select the last 4 rows of data from a table [message #219931 is a reply to message #219930] Fri, 16 February 2007 15:35 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Anacedent, come on now, behave nicely Wink

Hi Tom,

To get the "last 4 records" you have to define what you mean by last, the last ones inserted? Altered? Alphabetically?

You can select the rows, order them descending by whatever definition you want, and keep the first 4 rows of that resultset (usually, you would do that by adding where rownum < 5).

Regards,
Sabine
Re: select the last 4 rows of data from a table [message #219932 is a reply to message #219931] Fri, 16 February 2007 16:22 Go to previous messageGo to next message
jrich
Messages: 35
Registered: February 2006
Member
Assuming by last you mean most recent date_time in your sample table, then...

SELECT *
FROM   (SELECT RANK() OVER(PARTITION BY id_number ORDER BY date_time DESC) rn,
               date_time,
               value,
               flag
        FROM   testa)
WHERE  rn <= 4;


Note this query might return more than 4 rows if you have multiple rows with the same date_time value.

JR
Re: select the last 4 rows of data from a table [message #219933 is a reply to message #219931] Fri, 16 February 2007 16:23 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
Thanks very much for your help Jrich.

The query retrieves the correct results that I was looking for.


Tom

[Updated on: Fri, 16 February 2007 16:28]

Report message to a moderator

Re: select the last 4 rows of data from a table [message #219938 is a reply to message #219933] Fri, 16 February 2007 16:53 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
I am going to have to disagree with your assessment this it worked the way you wanted it to. Yes, it may look like it returned the results you wanted, but it's just by luck.

You have non-unique ID numbers, and you asked to retrieve regardless of datetime, therefore you have no basis for selecting any order.
Additionally, I don't see how you can have a constraint for primary key on a column that doesn't even exist in your table.

Re: select the last 4 rows of data from a table [message #219940 is a reply to message #219929] Fri, 16 February 2007 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>The query retrieves the correct results that I was looking for.
SQL will ALWAYS return rows or report "No Rows Found".
HOWEVER the rows returned may not actually be the ones desired under all conditions depending upon what actually exist within the tables when the SELECT is issued.
Re: select the last 4 rows of data from a table [message #219941 is a reply to message #219938] Fri, 16 February 2007 17:02 Go to previous message
bztom33
Messages: 95
Registered: June 2005
Member
It's was a typo. The correct constraint should be id_number.
Thanks for pointing it out.



Previous Topic: Oracle 9i Catalog and Schema version
Next Topic: How to Import & Export Data in Excel sheet???Using oracle 8i&9i???
Goto Forum:
  


Current Time: Fri Dec 09 11:31:20 CST 2016

Total time taken to generate the page: 0.16469 seconds