0

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
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
Ray
  • 11
  • 1
  • 6
  • 1
    Why can't you use a where statement? – Andreas Jul 30 '14 at 19:37
  • If I use this where statement i would get multiple values base on timestamp. I just want the max value from this range of time. – Ray Jul 30 '14 at 19:50
  • Just remove the `timestamp,` from the SELECT statement in your query and you will get the max value as you expect. – Joseph B Jul 30 '14 at 20:13
  • I NEED the timestamp. That is the whole point of the question. Of course it is going to work if you just do a select max value. – Ray Jul 30 '14 at 20:15

2 Answers2

1

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.

Community
  • 1
  • 1
Nivas
  • 18,126
  • 4
  • 62
  • 76
  • I need the timestamp and when it occur as well. The timestamp is the part giving me the problem. The data is useless without the time it occured. – Ray Jul 30 '14 at 20:14
0

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).

Adam Porad
  • 14,193
  • 3
  • 31
  • 56
  • OP needs the TS corresponding to the max value, this gives the max value corresponding to the max TS. – Nivas Jul 30 '14 at 20:26
  • 1
    @Nivas You're right. I did include a comment that the HAVING clause could filter for the timestamp of the current day. I've corrected the query. Thanks for the quality review :) – Adam Porad Jul 30 '14 at 20:32
  • :-). BTW, `timestamp-for-today` is `SYSTIMESTAMP` or `TRUNC(SYSTIMESTAMP)` depending on whether you want only the date part (date and 00:00:00.0000) or the time part (current instant) – Nivas Jul 30 '14 at 23:02