group by with where clause

[brianlange]

My Oracle table columns are: message_id, status, start_time

 

There is no primary key. There are multiple rows for each message_id.

 

I would like to be able to select records where the record that has the mininum start_time value for records grouped by message_id has a status value of 'PC'

 

Thanks!

 

 

 

 

 

 

[Zane]

you mean like

SELECT status, start_time, message_id
FROM yourTable
ORDER BY start_time DESC
GROUP BY status

[brianlange]

I don't think that is going to work.

I only want the records where the min(start_time) for records grouped by message_id has a status value of 'PC'

I was able to get a result with the following. Is there a better way to do this?

 

select message_id from table where start_time in

(select min(start_time) from table group by message_id)

and status = 'PC'