Home » SQL & PL/SQL » SQL & PL/SQL » how to get Max(value) with its corresponding datetime ... (Oracle ...)
how to get Max(value) with its corresponding datetime ... [message #596399] Sun, 22 September 2013 11:33 Go to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
Hi,

This is my table
ID timestamp value
1 2013-09-09 01:09:00.000 1234
2 2013-09-09 02:00:00.000 123
1 2013-09-09 03:09:00.000 1233
2 2013-09-09 21:09:00.000 125

I need to find max(value) with its corresponding time stamp .. this table has approximately 500000 records with 180 distinct IDs. Need to find max(value) group by IDs. Please let me know the Query ...

Expected result:
ID timestamp value
1 2013-09-09 01:09:00.000 1234
2 2013-09-09 21:09:00.000 125

We have a query but its returns 00 in hh:mm:ss instead of exact timestamp.

Thanks.
Re: how to get Max(value) with its corresponding datetime ... [message #596400 is a reply to message #596399] Sun, 22 September 2013 11:35 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
icon4.gif  Re: how to get Max(value) with its corresponding datetime ... [message #596403 is a reply to message #596399] Sun, 22 September 2013 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what if there are several rows with the max value?
Here's an example:
SQL> select empno, ename, sal,
  2         row_number() over (order by sal desc) "ROW_NUMBER",
  3         rank() over (order by sal desc) "RANK",
  4         dense_rank() over (order by sal desc) "DENSE_RANK"
  5  from emp
  6  /
     EMPNO ENAME             SAL ROW_NUMBER       RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ----------
      7839 KING             5000          1          1          1
      7902 FORD             3000          2          2          2
      7788 SCOTT            3000          3          2          2
      7566 JONES            2975          4          4          3
      7698 BLAKE            2850          5          5          4
      7782 CLARK            2450          6          6          5
      7499 ALLEN            1600          7          7          6
      7844 TURNER           1500          8          8          7
      7934 MILLER           1300          9          9          8
      7521 WARD             1250         10         10          9
      7654 MARTIN           1250         11         10          9
      7876 ADAMS            1100         12         12         10
      7900 JAMES             950         13         13         11
      7369 SMITH             800         14         14         12

what you want is one with some rank equals to 1.
Re: how to get Max(value) with its corresponding datetime ... [message #596404 is a reply to message #596400] Sun, 22 September 2013 13:00 Go to previous messageGo to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
Hi Michel,

Thanks for the reply.
But in my case there will never be such a scenario ... The value is a decimal ...
Please get me the query to find the max (group by ID).

This is my table
ID timestamp value
1 2013-09-09 01:09:00.000 1234
2 2013-09-09 02:00:00.000 123
1 2013-09-09 03:09:00.000 1233
2 2013-09-09 21:09:00.000 125

I need to find max(value) with its corresponding time stamp .. this table has approximately 500000 records with 180 distinct IDs. Need to find max(value) group by IDs. Please let me know the Query ...

Expected result:
ID timestamp value
1 2013-09-09 01:09:00.000 1234
2 2013-09-09 21:09:00.000 125

Thanks.
Re: how to get Max(value) with its corresponding datetime ... [message #596405 is a reply to message #596404] Sun, 22 September 2013 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
since we don't have your table or data, we can not write your SQL
icon2.gif  Re: how to get Max(value) with its corresponding datetime ... [message #596406 is a reply to message #596404] Sun, 22 September 2013 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you just change the name of the columns and table in my query and use one of the ranking functions (any one as you say you have no same value) then you have the query you have to use as an inline view and then, as I said, just return the rows with rank 1.

I can't write a query for table and columns I have not, you have to write it with the explanations and example I gave.

Re: how to get Max(value) with its corresponding datetime ... [message #596407 is a reply to message #596406] Sun, 22 September 2013 14:31 Go to previous messageGo to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
ok here is the table

CREATE TABLE Frequency (

table_index number,

timestamp datetime,

value decimal

)

This is my Sample data in table:
table_index timestamp value
1 2013-09-09 01:09:00.000 1234
2 2013-09-09 02:00:00.000 123
1 2013-09-09 03:09:00.000 1233
2 2013-09-09 21:09:00.000 125

I need to find max(value) with its corresponding time stamp .. this table has approximately 500000 records with 180 distinct table_index. Need to find max(value) group by table_index. Please let me know the Query ...

Expected result:
table_index timestamp value
1 2013-09-09 01:09:00.000 1234
2 2013-09-09 21:09:00.000 125

Thanks.

[Updated on: Sun, 22 September 2013 14:35]

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596408 is a reply to message #596407] Sun, 22 September 2013 14:34 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member

  1  CREATE TABLE Frequency (
  2  table_index number,
  3  order_date datetime,
  4  value decimal
  5* )
SQL> /
order_date datetime,
           *
ERROR at line 3:
ORA-00902: invalid datatype



It appears that you don't really have Oracle database.

post complete results from SQL below

SELECT * FROM V$VERSION;
Re: how to get Max(value) with its corresponding datetime ... [message #596409 is a reply to message #596407] Sun, 22 September 2013 14:38 Go to previous messageGo to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
CREATE TABLE Frequency (

table_index number,

timestamp date,

value decimal

)
Re: how to get Max(value) with its corresponding datetime ... [message #596410 is a reply to message #596409] Sun, 22 September 2013 14:42 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
It would be helpful if you provided DML (INSERT INTO ...) for test data.

post data is does NOT conform to DATE datatype; which does not contain any fractions of seconds.

Why do you waste our time by posting bogus SQL & data?
icon1.gif  Re: how to get Max(value) with its corresponding datetime ... [message #596411 is a reply to message #596409] Sun, 22 September 2013 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

select table_index, timestamp, value 
from (select table_index, timestamp, value, 
             row_number() over (partition by table_index order by timestamp desc) rn
      from Frequency)
where rn = 1
/
Re: how to get Max(value) with its corresponding datetime ... [message #596412 is a reply to message #596411] Sun, 22 September 2013 15:01 Go to previous messageGo to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
Thank you very much ...

Should it be order by timestamp desc Or
order by value desc
since i need max(value).
icon2.gif  Re: how to get Max(value) with its corresponding datetime ... [message #596414 is a reply to message #596412] Sun, 22 September 2013 15:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, max(value), this is why we ask for a complete test case with its result, to avoid such error.

Re: how to get Max(value) with its corresponding datetime ... [message #596498 is a reply to message #596414] Tue, 24 September 2013 00:33 Go to previous messageGo to next message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
I tried below queries but get error:

select * from (Select table_index,hist_timestamp, max(value) Value,row_number() over(partition by table_index order by Value desc)rn from freq where
hist_timestamp >= to_date('09/20/2013 19:00:00', 'mm/dd/yyyy hh24:mi:ss') AND
hist_timestamp < to_date('09/20/2013 20:00:00', 'mm/dd/yyyy hh24:mi:ss') order by table_index) where rn=1;

ORA-00937: not a single-group group function
======================================================================
select * from (Select table_index,hist_timestamp, max(value) Value,row_number() over(partition by table_index order by Value desc)rn from freq where
hist_timestamp >= to_date('09/20/2013 19:00:00', 'mm/dd/yyyy hh24:mi:ss') AND
hist_timestamp < to_date('09/20/2013 20:00:00', 'mm/dd/yyyy hh24:mi:ss') group by table_index,hist_timestamp) where rn=1;

ORA-00979: not a GROUP BY expression
========================================================================

With e As (Select *,row_number() over (partition by table_index order by Value desc) as Recency from freq where
hist_timestamp >= to_date('09/20/2013 19:00:00', 'mm/dd/yyyy hh24:mi:ss') AND
hist_timestamp < to_date('09/20/2013 20:00:00', 'mm/dd/yyyy hh24:mi:ss'))
select * from e where Recency = 1;
ORA-00923: FROM keyword not found where expected
icon2.gif  Re: how to get Max(value) with its corresponding datetime ... [message #596499 is a reply to message #596498] Tue, 24 September 2013 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't need MAX(VALUE), you automatically reaches the row with this value with RN=1.

Re: how to get Max(value) with its corresponding datetime ... [message #596503 is a reply to message #596499] Tue, 24 September 2013 02:02 Go to previous message
yasinirshad
Messages: 12
Registered: January 2013
Junior Member
Thanks.
Previous Topic: cursor implement
Next Topic: Increment sequnce no based on calender date.
Goto Forum:
  


Current Time: Thu Oct 02 10:58:49 CDT 2014

Total time taken to generate the page: 0.24381 seconds