Select query earliest child record by date

Tetelestai

I would like help with the syntax of a query that will return 1 childs' record field that has the earliest date. I assume this could be done in a query.

example of tables:
tblOrder - Parent Table
OrderID - PK
OrderModel
OrderDesigner

tblFloor - Child Table (One or More instances)
FloorID - PK
OrderID - FK
FloorDueDate
FloorComplete

I would like to select the earliest due, FloorDueDate where the Floor is not complete and use it as the Parents' due date. I

I've looked at DISTINCTROW, DISTINCT and TOP and not sure how to put them together.

Rabbit

Quote:

Originally Posted by Tetelestai

I would like help with the syntax of a query that will return 1 childs' record field that has the earliest date. I assume this could be done in a query.

example of tables:
tblOrder - Parent Table
OrderID - PK
OrderModel
OrderDesigner

tblFloor - Child Table (One or More instances)
FloorID - PK
OrderID - FK
FloorDueDate
FloorComplete

I would like to select the earliest due, FloorDueDate where the Floor is not complete and use it as the Parents' due date. I

I've looked at DISTINCTROW, DISTINCT and TOP and not sure how to put them together.

Are you looking for a specific OrderID? In which case you would indeed use TOP.
Or are you looking to return for all OrderID's? In which case you would use TOP in a subquery.

Tetelestai

Thanks Rabbit

I looked up your other post as well. Here is what I have so far. This is my actual tables and field names:

Expand|Select|Wrap|Line Numbers

 
SELECT [JobID] & "-" & [JobSubID] AS Job, Ordr.Model, Ordr.JobReference, tblFloors.Floor, tblFloors.DesSchDate

FROM tblOrders AS Ordr INNER JOIN tblFloors 

ON Ordr.JobOrderID = tblFloors.JobOrderID

WHERE tblFloors.DesSchDate 

IN (SELECT TOP 1 tblFloors.DesSchDate FROM tblFloors 

WHERE tblFloors.JobOrderID = Ordr.JobOrderID 

and tblFloors.Designed=False

ORDER BY tblFloors.DesSchDate);

Now TOP 1 will return more than 1 record (per JobOrderID) if they (DesSchDate) happen to be on the same date.
Where do I put a DISTINCT predicate to just return one?

Rabbit

Quote:

Originally Posted by Tetelestai

Thanks Rabbit

I looked up your other post as well. Here is what I have so far. This is my actual tables and field names:

Expand|Select|Wrap|Line Numbers

 
SELECT  [JobID] & "-" & [JobSubID] AS Job, Ordr.Model, Ordr.JobReference, tblFloors.Floor, tblFloors.DesSchDate

FROM  tblOrders AS Ordr INNER JOIN tblFloors ON Ordr.JobOrderID = tblFloors.JobOrderID

WHERE tblFloors.DesSchDate IN (SELECT TOP 1 tblFloors.DesSchDate 

                                                        FROM tblFloors 

                                                        WHERE tblFloors.JobOrderID = Ordr.JobOrderID  and tblFloors.Designed=False

                                                        ORDER BY tblFloors.DesSchDate);

Now TOP 1 will return more than 1 record (per JobOrderID) if they (DesSchDate) happen to be on the same date.
Where do I put a DISTINCT predicate to just return one?

You're getting duplicate records? In this case you would put DISTINCT in the main query. The subquery will never return more than one record. It's happening because the main query has nonunique records.

Tetelestai

Quote:

Originally Posted by Rabbit

You're getting duplicate records? In this case you would put DISTINCT in the main query. The subquery will never return more than one record. It's happening because the main query has nonunique records.

Doesn't TOP 1 return more than one when there is a tie (the same date in this case)?

Example output where there is a tie.

Expand|Select|Wrap|Line Numbers

 
Job    Model    JobReference    Floor    DesSchDate

LR3-SG    1545                      1st Floor    6/6/2007

LR3-SG    1545                      2nd Floor    6/6/2007

Rabbit

Quote:

Originally Posted by Tetelestai

Doesn't TOP 1 return more than one when there is a tie (the same date in this case)?

Example output where there is a tie.

Expand|Select|Wrap|Line Numbers

 
Job    Model    JobReference    Floor    DesSchDate

LR3-SG    1545                      1st Floor    6/6/2007

LR3-SG    1545                      2nd Floor    6/6/2007

Top 1 will return 1 record regardless of a tie.

Top X returns the first X records after it sorts. If there's a tie, it doesn't return more records. In your example it will return the top 1 for job LR3-SG and returns the same top 1 for the next LR3-SG. It's because you're only looking at Job as the sole discriminating factor. Because of this, it never looks at any other variable.

Tetelestai

Quote:

Originally Posted by Rabbit

Top 1 will return 1 record regardless of a tie.

Top X returns the first X records after it sorts. If there's a tie, it doesn't return more records. In your example it will return the top 1 for job LR3-SG and returns the same top 1 for the next LR3-SG. It's because you're only looking at Job as the sole discriminating factor. Because of this, it never looks at any other variable.

Is this just because of my construction of the query? Cause this is right out of the access help (2007).

Quote:

The TOP predicate does not choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.

Rabbit

Quote:

Originally Posted by Tetelestai

Is this just because of my construction of the query? Cause this is right out of the access help (2007).

It might be a quirk of 2007. I tried it in 2003 and it only returned one record.

Edit: So it does return more than 1 record. I had originally tried it with a date value and that only returned 1 record. But date values are weird like that. I tried it with a string value and it returned more than one record.

Rabbit

In this case, instead of TOP, use the aggregate Max or Min, depending on what you need.

Select query earliest child record by date

re: Select query earliest child record by date

re: Select query earliest child record by date

re: Select query earliest child record by date

re: Select query earliest child record by date

re: Select query earliest child record by date

re: Select query earliest child record by date

re: Select query earliest child record by date

re: Select query earliest child record by date

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about