OraFAQ Forum: SQL & PL/SQL » how to get Max(value) with its corresponding datetime ...

Home » SQL & PL/SQL » SQL & PL/SQL » how to get Max(value) with its corresponding datetime ... (Oracle ...)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to get Max(value) with its corresponding datetime ... [message #596399]

Sun, 22 September 2013 11:33

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.

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596400 is a reply to message #596399]

Sun, 22 September 2013 11:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596403 is a reply to message #596399]

Sun, 22 September 2013 12:28

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596404 is a reply to message #596400]

Sun, 22 September 2013 13:00

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.

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596405 is a reply to message #596404]

Sun, 22 September 2013 13:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

since we don't have your table or data, we can not write your SQL

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596406 is a reply to message #596404]

Sun, 22 September 2013 13:22

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596407 is a reply to message #596406]

Sun, 22 September 2013 14:31

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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;

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596409 is a reply to message #596407]

Sun, 22 September 2013 14:38

yasinirshad
Messages: 12
Registered: January 2013

Junior Member

CREATE TABLE Frequency (

table_index number,

timestamp date,

value decimal

)

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596410 is a reply to message #596409]

Sun, 22 September 2013 14:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596411 is a reply to message #596409]

Sun, 22 September 2013 14:52

Michel Cadot
Messages: 68645
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
/

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596412 is a reply to message #596411]

Sun, 22 September 2013 15:01

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

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596414 is a reply to message #596412]

Sun, 22 September 2013 15:32

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596498 is a reply to message #596414]

Tue, 24 September 2013 00:33

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

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596499 is a reply to message #596498]

Tue, 24 September 2013 01:12

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: how to get Max(value) with its corresponding datetime ... [message #596503 is a reply to message #596499]

Tue, 24 September 2013 02:02

yasinirshad
Messages: 12
Registered: January 2013

Junior Member

Thanks.

Report message to a moderator

Previous Topic:	cursor implement
Next Topic:	Increment sequnce no based on calender date.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:04:06 CDT 2024