oracle query to get max hour every day, and corresponding row values

I'm having a hard time creating a query to do the following:

I have this table, called LOG:

ID | INSERT_TIME             | LOG_VALUE
----------------------------------------
 1 | 2013-04-29 18:00:00.000 | 160473
 2 | 2013-04-29 21:00:00.000 | 154281
 3 | 2013-04-30 09:00:00.000 | 186552
 4 | 2013-04-30 14:00:00.000 | 173145
 5 | 2013-04-30 14:30:00.000 | 102235
 6 | 2013-05-01 11:00:00.000 | 201541
 7 | 2013-05-01 23:00:00.000 | 195234

What I want to do is build a query that returns, for each day, the last values inserted (using the max value of INSERT_TIME). I'm only interested in the date part of that column, and in the column LOG_VALUE. So, this would be my resultset after running the query:

2013-04-29  154281
2013-04-30  102235
2013-05-01  195234

I guess that I need to use GROUP BY over the INSERT_TIME column, along with MAX() function, but by doing that, I can't seem to get the LOG_VALUE. Can anyone help me on this, please?

(I'm on Oracle 10g)

Answers


SELECT trunc(insert_time),
       log_value
  FROM (
    SELECT insert_time,
           log_value,
           rank() over (partition by trunc(insert_time)
                            order by insert_time desc) rnk
      FROM log)
 WHERE rnk = 1

is one option. This uses the analytic function rank to identify the row with the latest insert_time on each day.


Need Your Help

Generating Primes by 'Sieve' method

c primes code-review

I am trying to solve this problem http://www.spoj.com/problems/PRIME1/ ... My implementation is as follows :

Core Data “The Database appears corrupt” — What causes this error?

ios objective-c sqlite core-data nsmanagedobjectcontext

I'm banging my head against the wall here, I'm using Core Data for a SQLLite DB, and I am able to successfully save to the database (I've checked the contents in an offline SQLLite browser), but af...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.