Need help in selecting multiple entries
Hello All,
I am trying to create a sql query which looks for entries in database that are created previous day and capture highest Status entry.
My Database structure and some sample data:
ServiceName | OCCURRENCE | STATUS
xxx |2012-06-27 17:31:42 | 3
aaa |2012-06-27 12:03:00 | 4
ppp |2012-06-26 01:21:14 | 5
ppp |2012-06-27 07:36:00 | 2
aaa |2012-06-27 05:52:18 | 5
My Query:
select
SERVICENAME,
OCCURENCE,
max(STATUS) as STATUS,
from
MyTable
where
DAY( OCCURENCE ) = EXTRACT(DAY FROM (NOW() - INTERVAL 1 DAY))
group by
SERVICENAME
With this query I am able to get maximum Status value for particular day but the other fields are not matching that Status value
Example output:
ServiceName | OCCURRENCE | STATUS
xxx |2012-06-27 17:31:42 | 3
aaa |2012-06-27 12:03:00 | 5
ppp |2012-06-27 07:36:00 | 5
The timing is mis-matched with Status. Can you help me in modifying my query and get correct output.
Thanks.
Subject
Written By
Posted
Need help in selecting multiple entries
June 28, 2012 01:35PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.