CODEWITHSUNDEEP
sqloracle-database
Ray
Ray

Reputation: 21

Grab max only from today's data

I have a set of data that looks like this. Is there a way to only grab the max value from today's date without using a where statement?

I was using the script below but it seems to conflict and gets me all the timestamps and the highest value from each timestamp. If someone knows a way to select the max value from today in one/two lines that would be great. Thank you

Select timestamp,
       max(value)
  FROM Table1
 WHERE r.timestamp > ( ( SYSDATE - TO_DATE('01/01/1970 00:00:00',
                                           'MM-DD-YYYY HH24:MI:SS') ) 
                        * 24 * 60 * 60) - 57600; 


 TIMESTAMP      VALUE
---------- ----------
1359574942          1
1359574942         12
1359575012          0
1359575012          0
1359575122          9
1359575212          0

Upvotes: 0

Views: 69

Answers (2)

Adam Porad
Adam Porad

Reputation: 14471

I think if you just need to include a HAVING clause to your query to only get rows with equal to time stamp of the current day.

SELECT r.timestamp,
       max(r.value)
  FROM Table1 r
 WHERE r.timestamp > ( ( SYSDATE - TO_DATE('01/01/1970 00:00:00','MM-DD-YYYY HH24:MI:SS') ) * 24 * 60 * 60) - 57600 
GROUP BY r.timestamp
HAVING r.timestamp = <timestamp-for-today>

Note: You need to fix the <timestamp-for-today> in the HAVING clause. I'm not quite sure what the calculation for that is (I didn't put much time into figuring that out).

Upvotes: 0

Nivas
Nivas

Reputation: 18344

If you need only the max value of today, all you need is

select max(value) 
from table1
where timestamp > trunc(systimestamp);

If your table can have data for the future (timestamp>systimestamp),

select max(value) 
from table1
where timestamp > trunc(systimestamp)
      and timestamp < trunc(systimestamp) + 1;

A quick solution to get the corresponding timestamp as well,

select * from (
    select timestamp, max(value) m
    from table1
    where ts > trunc(systimestamp)
    group by timestamp
    order by m desc
) where rownum < 2;

See How to do top 1 in Oracle for other possible options.

Upvotes: 1

Related Questions