stuck with the sql / hql query group by

Question

id          device_id   config_status   update_finished
1           5015001     SUCCESS     11-OCT-2012
2           5015001     SUCCESS     23-OCT-2012
3           5015001     PENDING     23-OCT-2012
4           5015001     PENDING     23-OCT-2012
5           5016222     PENDING     12-OCT-2012
6           5016222     PENDING     22-OCT-2012

Hi, above is my table with some example data. What I'd like to achieve is to get for each device_id last SUCCESS status (date is in update_finished field) and all PENDING statuses. From the example above the result query should have:

id          device_id   config_status   update_finished
2           5015001     SUCCESS     23-OCT-2012
3           5015001     PENDING     23-OCT-2012
4           5015001     PENDING     23-OCT-2012
5           5016222     PENDING     12-OCT-2012
6           5016222     PENDING     22-OCT-2012

So that means only the last SUCCESS status by date column update_finished and all PENDING statuses for each device_id.

Currently I'm trying to mess with GROUP BY: SELECT device_id, config_status, max(update_finished) AS "LastUpdate" from config_status WHERE config_status = 'SUCCESS' group by device_id, config_status

This returns only the last SUCCESS statuses for each device_id indeed, but it misses the PENDING statuses and it doesn't have id column. If I add id field to the GROUP BY then the grouping would be by id anyway.

Could anyone help me with the proper query? Is it supposed to have subqueries also? In the end I need to have it as HQL, but I think it will be very similar to SQL.

EDIT:

Thanks everybody for helping me out. All these queries work but most fitted my the query provided by @Remigio because it was easiest to transform it into HQL query that returns me parametrized object, not just Object.

Here is my working HQL query with additional fields that I have omitted in my initial example

List<ConfigStatus> statuses = getHibernateTemplate().find("select c from ConfigStatus c " +
                "where (configStatus = 'SUCCESS' and updateFinished = (select max(updateFinished) from " +
                "ConfigStatus c1 where c1.device.deviceId = c.device.deviceId and c1.configStatus = 'SUCCESS' and c1.configFile.configFileId = ?)) " +
                "or configStatus = 'PENDING' and configFile.configFileId = ?", new Object[] {configFileId, configFileId});

Answer 1

You can try this query:

select * from device d
where (config_status='SUCCESS' and update_finished = (select max(update_finished) from
device d1 where d1.device_id = d.device_id and d1.config_status='SUCCESS'))
or config_status='PENDING'

Answer 2

Based on oracle sample:

SELECT last_name FROM 
   (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
   WHERE R BETWEEN 51 and 100;

Yout query should be:

select * from (
  SELECT device_id, config_status, update_finished AS "LastUpdate" ,
  ROW_NUMBER() OVER (ORDER BY update_finished desc) R
  from config_status WHERE config_status = 'SUCCESS' 
) where R = 1

EDITED

select device_id, config_status, update_finished
from (
  SELECT device_id, config_status, update_finished  ,
  ROW_NUMBER() OVER (ORDER BY update_finished desc) R
  from config_status WHERE config_status = 'SUCCESS' 
) where R = 1
union all
SELECT device_id, config_status, update_finished  
from config_status WHERE config_status = 'PENDING'