Home » SQL & PL/SQL » SQL & PL/SQL » Grabbing most recent data
Grabbing most recent data [message #246954] Fri, 22 June 2007 10:50 Go to next message
cpmckinn
Messages: 3
Registered: June 2007
Junior Member
Hi,
I am working with a large database containing data from tests conducted on a number of devices. The tests for each device are all run at different times, and there can be multiple tests in the database for a given device. What I would like to do is to grab only the most recent rows of data for each device ID. I had tried to accomplish this by selecting max(time) and grouping by the device ID, but SQL doesn't seem to like having multiple rows with the same ID and timestamp in this case.

Thanks!
Cameron
Re: Grabbing most recent data [message #246956 is a reply to message #246954] Fri, 22 June 2007 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SQL doesn't seem to like having multiple rows with the same ID and timestamp in this case

It seems to me your query is wrong but who knows and who can help you if you don't post it?

Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Grabbing most recent data [message #246959 is a reply to message #246954] Fri, 22 June 2007 11:07 Go to previous messageGo to next message
cpmckinn
Messages: 3
Registered: June 2007
Junior Member
My fault. Here would be a stripped-down query that I have tried then.

Select ID, max(timestamp)
from table
group by ID

The server gives the error "not a GROUP BY expression."
The following query:

select ID,data1,data2,max(timestamp)
from table
group by ID,data1,data2

works, but it selects the latest point sharing the same value for ID, data1, and data2. The server seems to have no troubles here because there are no multiples of rows in the database with the same ID, data1,data2, and timestamp.

I have the Oracle 10.2.0 client installed on this computer.

Cameron

[Updated on: Fri, 22 June 2007 11:09]

Report message to a moderator

Re: Grabbing most recent data [message #246963 is a reply to message #246959] Fri, 22 June 2007 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from mytable
where (id,timestamp) in 
      (select id,max(timestamp) ...)
/

Btw, it is SERVER version which is useful because SQL executes on server not client.
Also, read the link I posted on how to format your post.

Regards
Michel

Re: Grabbing most recent data [message #246974 is a reply to message #246954] Fri, 22 June 2007 11:52 Go to previous messageGo to next message
cpmckinn
Messages: 3
Registered: June 2007
Junior Member
Michel,
Everything seems to be in order now, thanks for your help and prompt replies. I'll be sure to provide better info and formatting in the future.
Cameron
Re: Grabbing most recent data [message #246980 is a reply to message #246959] Fri, 22 June 2007 12:51 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
cpmckinn wrote on Fri, 22 June 2007 12:07

Select ID, max(timestamp)
from table
group by ID

The server gives the error "not a GROUP BY expression."



I find it hard to believe that SQL above will give that error. Why not just cut and paste a session that retype your code which is prone to error?
Previous Topic: Proper way to create this procedure
Next Topic: create a view in schema1 that references a view in schema2
Goto Forum:
  


Current Time: Wed Dec 07 05:16:46 CST 2016

Total time taken to generate the page: 0.08636 seconds