Hi, This is called a "Top-N Query", because you want to pick N items (N=1 in this case, counting ties) from the top of an ordered list (ordered by finish_date, then by task_id in this case). Different databases have different ways of doing this. Oracle, for example, has a function called RANK that is very useful for Top-N queries. Here's how you can use RANK on your data to get the results you want: <pre> SELECT proj_id , task_id , finish_date FROM ( -- Begin in-line view to compute r_num SELECT proj_id , task_id , finish_date , RANK () OVER ( PARTITION BY proj_id ORDER BY finish_date, task_id ) AS r_num FROM table_x ) -- End in-line view to compute r_num WHERE r_num = 1 ORDER BY proj_id; </pre> Here's another way to do the same thing. As you can see, it's a lot messier, but it only uses standard SQL features: <pre> SELECT * FROM table_x WHERE (proj_id, task_id, finish_date) IN ( -- Begin sub-query to find min_task_id SELECT proj_id , MIN (task_id) AS min_task_id , finish_date FROM table_x WHERE (proj_id, finish_date) IN ( -- Begin sub-query to calculate min_finish_date SELECT proj_id , MIN (finish_date) AS min_finish_date FROM table_x GROUP BY proj_id ) -- End sub-query to calculate min_finish_date GROUP BY proj_id , finish_date ) -- End sub-query to find min_task_id ORDER BY proj_id ; </pre>

Hi, This is called a "Top-N Query", because you want to pick N items (N=1 in this case, counting ties) from the top of an ordered list (ordered by finish_date, then by task_id in this case). Different databases have different ways of doing this. Oracle, for example, has a function called RANK that is very useful for Top-N queries. Here's how you can use RANK on your data to get the results you want: <pre> SELECT proj_id , task_id , finish_date FROM ( -- Begin in-line view to compute r_num SELECT proj_id , task_id , finish_date , RANK () OVER ( PARTITION BY proj_id ORDER BY finish_date, task_id ) AS r_num FROM table_x ) -- End in-line view to compute r_num WHERE r_num = 1 ORDER BY proj_id; </pre> Here's another way to do the same thing. As you can see, it's a lot messier, but it only uses standard SQL features: <pre> SELECT * FROM table_x WHERE (proj_id, task_id, finish_date) IN ( -- Begin sub-query to find min_task_id SELECT proj_id , MIN (task_id) AS min_task_id , finish_date FROM table_x WHERE (proj_id, finish_date) IN ( -- Begin sub-query to calculate min_finish_date SELECT proj_id , MIN (finish_date) AS min_finish_date FROM table_x GROUP BY proj_id ) -- End sub-query to calculate min_finish_date GROUP BY proj_id , finish_date ) -- End sub-query to find min_task_id ORDER BY proj_id ; </pre>