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]

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:

[code=sql]
SELECT [JobID] & "-" & [JobSubID] AS Job, Ordr.Model, Ordr.JobReferen ce, tblFloors.Floor , tblFloors.DesSc hDate
FROM tblOrders AS Ordr INNER JOIN tblFloors
ON Ordr.JobOrderID = tblFloors.JobOr derID
WHERE tblFloors.DesSc hDate
IN (SELECT TOP 1 tblFloors.DesSc hDate FROM tblFloors
WHERE tblFloors.JobOr derID = Ordr.JobOrderID
and tblFloors.Desig ned=False
ORDER BY tblFloors.DesSc hDate);
[/code]

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]

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:

[code=sql]
SELECT [JobID] & "-" & [JobSubID] AS Job, Ordr.Model, Ordr.JobReferen ce, tblFloors.Floor , tblFloors.DesSc hDate
FROM tblOrders AS Ordr INNER JOIN tblFloors ON Ordr.JobOrderID = tblFloors.JobOr derID
WHERE tblFloors.DesSc hDate IN (SELECT TOP 1 tblFloors.DesSc hDate
FROM tblFloors
WHERE tblFloors.JobOr derID = Ordr.JobOrderID and tblFloors.Desig ned=False
ORDER BY tblFloors.DesSc hDate);
[/code]

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]

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.

Job	Model	JobReference	Floor	DesSchDate
LR3-SG	1545		              1st Floor	6/6/2007
LR3-SG	1545		              2nd Floor	6/6/2007

[Rabbit]

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.

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]

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).

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]

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.