MySQL Forums
Forum List  »  MySQL Query Browser

Need help in selecting multiple entries
Posted by: Srinivas Naram
Date: June 28, 2012 01:35PM

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.

Options: ReplyQuote


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.