CODEWITHSUNDEEP
sqlsqliteoracle-sqldeveloper
Ray
Ray

Reputation: 21

SQL . Timestamp issue with max value

Hello I have a sql script like this. My intention of the script is to produce out the highest value of today. However the script produce out an unintended result. Can anyone help me look at my code and see what is wrong with it ?

Script

SELECT   MAX(Value),
         TIMESTAMP,
         fooditem,
         cashcurren
FROM     farm1
WHERE    r.timestamp > 1405987200
         AND r.timestamp <= (1405987200 + 86400)
         AND fooditem = '2'
         AND cashcurren = '10'
GROUP BY timestamp, fooditem, cashcurren;

The Unintended Result

Value Timestamp    fooditem   cashcurren

 200 1406029354          2         10
  84 1406034965          2         10
 536 1406034973          2         10
  70 1406035006          2         10
  63 1406035025          2         10

The Result I want

Value Timestamp fooditem cashcurren

 536 1406034973          2         10

Basically I want my Oracle SQL to return back the highest value for food item #2 and cashcurrency #10 from the timestamp 1405987200 to 1405987200 + 86400 (the timestamp is the whole day of 7/22 in this case).

Upvotes: 0

Views: 222

Answers (3)

Christian Phillips
Christian Phillips

Reputation: 18769

    SELECT   d.Value, d.TIMESTAMP, d.fooditem, d.cashcurren
    FROM     (SELECT   MAX(Value) AS 'Value',
                   TIMESTAMP,
                   fooditem,
                   cashcurren
          FROM     farm1
          WHERE    r.timestamp > 1405987200
                   AND r.timestamp <= (1405987200 + 86400)
                   AND fooditem = '2'
                   AND cashcurren = '10'
          GROUP BY timestamp, fooditem, cashcurren) AS d
    ORDER BY d.Valuse DESC limit 1;

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

SELECT Value, TIMESTAMP, fooditem, cashcurren
  FROM farm1 f
 WHERE timestamp between 1405987200 and (1405987200 + 86400)
   AND fooditem = '2'
   AND cashcurren = '10'
 where value =
       (select max(x.value)
          from farm1 x
         where x.timestamp between 1405987200 and (1405987200 + 86400)
           and x.fooditem = f.fooditem
           and x.cashcurren = f.cashcurren)

Using max(value) and grouping by timestamp does not lead to any aggregation and does not make sense. (there is likely only one per timestamp)

The above query uses a subquery to select the max value for the given timestamp range, fooditem, and cashcurren, and then feeds that value to the query in the where clause.

Upvotes: 1

Andreas
Andreas

Reputation: 5103

SELECT   MAX(Value),
         TIMESTAMP,
         fooditem,
         cashcurren
FROM     farm1
WHERE    r.timestamp > 1405987200
         AND r.timestamp <= (1405987200 + 86400)
         AND fooditem = '2'
         AND cashcurren = '10'
GROUP BY timestamp, fooditem, cashcurren
order by 1 desc limit 1;

Upvotes: 0

Related Questions