CODEWITHSUNDEEP
sqloracle10g
Ray
Ray

Reputation: 21

SQL . Selecting highest value from a range of timestamp

I have a code like this. My intended result is to grab the highest value from a set range of timestamps. I'm not sure why this isn't working. I am using Oracles SQL 10. Thank you in advance for your help.

Code

 SELECT value, timestamp
      FROM farm1 f
     WHERE timestamp between 1405987200 and (1405987200 + 86400)
     where value =
           (select max(x.value)
             from farm1 x
             where x.timestamp between 1405987200 and (1405987200 + 86400);

Expected Results 

 TIMESTAMP        VALUE
----------    ----------

1406056898         8.09

Results it is producing 

 TIMESTAMP        VALUE
----------    ----------
1405992437         6.49
1406056898         8.09
1406055371         9.4
1406071600         1.12

In addition is there a way to grab today's timestamps only without using unix time such as 1405992437 ?

Upvotes: 0

Views: 60

Answers (1)

Sebas
Sebas

Reputation: 21542

SELECT MAX(value)
FROM farm1
WHERE timestamp BETWEEN 1405987200 AND (1405987200 + 86400)

?

And for your second question, do you mean using human readable comparisons? You can always use WHERE TO_DATE(timestamp,...) but I'm not sure about how your indexes would react after that.

Upvotes: 1

Related Questions