Stored Procedure Help

How can I express this?

I have a query that is a LEFT OUTER JOIN and using GroupBy and it is returning:

Job   Date               Quantity
100   1/19/2006      75
100   10/28/2006    100

How do I get it to return only the line with the min. date?

Job   Date               Quantity
100   1/19/2006      75


SELECT     Job.Job, MIN(Delivery.Promised_Date) AS Promised_Date, Delivery.Promised_Quantity
FROM         Job LEFT OUTER JOIN
                      Delivery ON Job.Job = Delivery.Job
GROUP BY Job.Job, Delivery.Promised_Quantity

Interesting.  I notice that if you have two deliveries of the same amount for a given job, your SQL will only show one.

You want to show the first delivery for each job?

SELECT
    Job.Job, MIN(Delivery.Promised_Date) AS FirstDelivery_Date
FROM
    Job LEFT OUTER JOIN
        Delivery ON Job.Job = Delivery.Job
GROUP BY
    Job.Job

If you want to add the quantity, you could make a view out of it:

CREATE VIEW Job_FirstDelivery
AS
SELECT
    Job.Job, MIN(Delivery.Promised_Date) AS FirstDelivery_Date
FROM
    Job LEFT OUTER JOIN
        Delivery ON Job.Job = Delivery.Job
GROUP BY
    Job.Job

Then use it in a new query:

SELECT
    Job.Job, Delivery.Promised_Date, Delivery.Promised_Quantity
FROM
    Job LEFT OUTER JOIN
        Delivery ON Job.Job = Delivery.Job
WHERE
    Delivery.Job IS NULL OR -- include jobs w/ no deliveries?
    EXISTS (
       SELECT
          *
       FROM
          Job_FirstDelivery
       WHERE
          Job_FirstDelivery.Job = Job.Job AND
          Job_FirstDelivery.FirstDelivery_Date = Delivery.Promised_Date
    )

If you have two deliveries for the same job on the same day, this will return all deliveries for that day... which is probably what you want.

It worked perfectly. I never used views before.

Thanks again

You could also try "top 1" to bring back only one row.

Why use views?

SELECT     Job.Job, Delivery.Promised_Date, Delivery.Promised_Quantity
FROM Job
[LEFT] JOIN  Delivery ON Job.Job = Delivery.Job
HAVING Promised_Date = (Select MIN(Delivery.Promised_Date)
    FROM Delivery)

Every delivery has a job right? So perhaps you should do a INNER JOIN instead.