Home » SQL & PL/SQL » SQL & PL/SQL » Create View with last 10080 records (oracle 9.2.0.8.0 Unix 11i)
Create View with last 10080 records [message #426570] Fri, 16 October 2009 12:31 Go to next message
nemsmith
Messages: 3
Registered: October 2009
Location: United States
Junior Member
HI,

I need to create a view for a table and want the view to exists of only the last 10,080 records of the records in the table. Can anyone help me with this?

Thanks,

Linda
Re: Create View with last 10080 records [message #426574 is a reply to message #426570] Fri, 16 October 2009 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "last".
Remember that a table is a heap, there is no first, no last.

Regards
Michel
Re: Create View with last 10080 records [message #426576 is a reply to message #426574] Fri, 16 October 2009 13:17 Go to previous messageGo to next message
nemsmith
Messages: 3
Registered: October 2009
Location: United States
Junior Member
Hi,
My boss has asked me to create a view of the last 10,080 records in table pxpmg021, and only for a given server id (which in this case will be 1). This table is continuously added to every minute. It contains records for server 1 and server 2, but he only wants to see the last 10,080 records at any given time for only server id of 1. I'm not sure how I would do this.

Thanks,

Linda
Re: Create View with last 10080 records [message #426582 is a reply to message #426576] Fri, 16 October 2009 14:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
"last" and "first" are meaningless, unless you use an order by.

Since you have to use an "order by" anway, you can just turn that around and display the first 10,080 records.

select * from 
(
  select * from table
   order by column desc
)
where rownum < 10081;


Edit: corrected syntax.

[Updated on: Fri, 16 October 2009 14:11]

Report message to a moderator

Re: Create View with last 10080 records [message #426588 is a reply to message #426582] Fri, 16 October 2009 14:32 Go to previous messageGo to next message
nemsmith
Messages: 3
Registered: October 2009
Location: United States
Junior Member
Hi Thomas,

Thank you very much, it looks like this will work.


Linda
Re: Create View with last 10080 records [message #426591 is a reply to message #426576] Fri, 16 October 2009 15:22 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
nemsmith wrote on Fri, 16 October 2009 14:17
Hi,
My boss has asked me to create a view of the last 10,080 records in table pxpmg021


Do you mean 10800, and not 10080 because 10800 is the number of seconds in 3 hours. Then it becomes trivial to get all record from the last 3 hours (if there is one per second as you previously mentioned).

[mod]

Oops, I see you said 1 per minute, not seconds, so 10800 would be the number of minutes in 7.5 days.

[Updated on: Fri, 16 October 2009 15:27]

Report message to a moderator

Re: Create View with last 10080 records [message #426690 is a reply to message #426591] Mon, 19 October 2009 00:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joy_division wrote on Fri, 16 October 2009 22:22

Oops, I see you said 1 per minute, not seconds, so 10800 would be the number of minutes in 7.5 days.

hehe.. and 10080 would be the number of minutes in one week Smile

Question here is do you want the last 10080 records for server 1 or do you want to only display server 1's records out of the last 10080 records?
In the latter case, you would display the records of the last (1 week / number of servers).
Re: Create View with last 10080 records [message #426765 is a reply to message #426690] Mon, 19 October 2009 08:24 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Frank wrote on Mon, 19 October 2009 01:41
joy_division wrote on Fri, 16 October 2009 22:22

Oops, I see you said 1 per minute, not seconds, so 10800 would be the number of minutes in 7.5 days.

hehe.. and 10080 would be the number of minutes in one week Smile


Double-hehe. I guess because I thought is said "seconds" and not "minutes" and 10800 was one of those numbers that somehow looked familiar, I tried to back into what 10800 minutes would mean.

I took the wrong fork in the road Wink
Previous Topic: How sysdate works?
Next Topic: datesheet query
Goto Forum:
  


Current Time: Tue Dec 06 12:24:50 CST 2016

Total time taken to generate the page: 0.12617 seconds