SQL to find minimum finish date

5 pts.
Tags:
SQL
Hi , I have table which contains project ID,task id and the finish date I need to get the minimum finish date for a each project id if a project id has multiple matches of finish date then i need to take the minimim fnish date of the minimum tak for that project id. E.g PROJ ID Task ID FINISH DATE 100 3 29/11/2007 100 4 29/11/2007 100 3 29/11/2007 200 2 28/11/2007 200 4 28/11/2007 200 2 27/11/2007 300 1 27/11/2007 300 9 27/11/2007 300 1 27/11/2007 Result of the query should be this PROID TASK ID FINISH DATE 100 3 29/11/2007 100 3 29/11/2007 200 2 27/11/2007 300 1 27/11/2007 300 1 27/11/2007 Note; All these needs to be done in a single query and the output should only be the flag when all these conditions are satisfied

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following