Question

Working with Oracle 11g here.

I m trying to figure out how to write a specific query against a sample table below:

 ID TYPE   PRIORITY    STATUS   DATE
 ----------------------------------------------------
 1  Q      A           NEW      01-OCT-2009
 1  Q      A           NEW      01-OCT-2009
 1  Q      A           NEW      01-OCT-2009
 1  Q      A           NEW      01-OCT-2009
 1  Q      A           NEW      01-OCT-2009
 1  Q      A           NEW      01-OCT-2009
 1  Q      A           NEW      01-OCT-2009
 1  Q      A           NEW      01-OCT-2009
 2  R      B           NEW      01-OCT-2009
 2  R      B           NEW      01-OCT-2009
 2  R      B           NEW      01-OCT-2009
 2  R      B           NEW      01-OCT-2009
 3  R      A           NEW      01-OCT-2009
 3  Q      A           NEW      01-OCT-2009
 3  Q      A           NEW      01-OCT-2009

Here is the pseudo-PL/SQL of what I want the query to do:

 SELECT ID, TYPE
 FROM DATA_TABLE
 WHERE ROWNUM = 1 AND STATUS =  NEW 
 GROUP BY ID, TYPE
 ORDER BY PRIORITY, DATE

I want to grab the next group of ID, TYPE that has a status NEW ordered by priority and date.

In the case above the statement should return either 1 Q or 3 Q, but not both, since they have the same priority and date. If 3 Q was set to STATUS= DONE then the query should return 1 Q.

For the second step I m going to join this data back in to the table to grab the rows for the set I want to process (eg: 1 Q). This doesn t have to be a two-step process; if I can grab the set of rows to process without the join that would be ideal.

I hope I m just missing something really simple, but I m open to using analytic functions for partitioning if need be.

Answer 1

SELECT * FROM (SELECT ID, TYPE FROM DATA_TABLE WHERE STATUS =  NEW  ORDER BY PRIORITY, DATE) WHERE ROWNUM = 1

That ought to work. You don t want to group by ID and TYPE because you re not actually trying to perform any sort of aggregation on the rows matching a given ID and TYPE. If you only cared about one column (say DATE) you could say

SELECT ID, TYPE FROM DATA_TABLE WHERE DATE = (SELECT MIN(DATE) FROM DATA_TABLE) AND ROWNUM = 1

and avoid sorting the whole table. But I don t see how to make that work here.

友情链接