By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,479 Members | 1,159 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,479 IT Pros & Developers. It's quick & easy.

Select query earliest child record by date

P: 34
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.
May 29 '07 #1
Share this Question
Share on Google+
8 Replies

Rabbit
Expert Mod 10K+
P: 12,441
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.
May 30 '07 #2

P: 34
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
  1. SELECT [JobID] & "-" & [JobSubID] AS Job, Ordr.Model, Ordr.JobReference, tblFloors.Floor, tblFloors.DesSchDate
  2. FROM tblOrders AS Ordr INNER JOIN tblFloors 
  3. ON Ordr.JobOrderID = tblFloors.JobOrderID
  4. WHERE tblFloors.DesSchDate 
  5. IN (SELECT TOP 1 tblFloors.DesSchDate FROM tblFloors 
  6. WHERE tblFloors.JobOrderID = Ordr.JobOrderID 
  7. and tblFloors.Designed=False
  8. ORDER BY tblFloors.DesSchDate);
  9.  
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?
May 31 '07 #3

Rabbit
Expert Mod 10K+
P: 12,441
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
  1. SELECT  [JobID] & "-" & [JobSubID] AS Job, Ordr.Model, Ordr.JobReference, tblFloors.Floor, tblFloors.DesSchDate
  2. FROM  tblOrders AS Ordr INNER JOIN tblFloors ON Ordr.JobOrderID = tblFloors.JobOrderID
  3. WHERE tblFloors.DesSchDate IN (SELECT TOP 1 tblFloors.DesSchDate 
  4.                                                         FROM tblFloors 
  5.                                                         WHERE tblFloors.JobOrderID = Ordr.JobOrderID  and tblFloors.Designed=False
  6.                                                         ORDER BY tblFloors.DesSchDate);
  7.  
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.
May 31 '07 #4

P: 34
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
  1. Job    Model    JobReference    Floor    DesSchDate
  2. LR3-SG    1545                      1st Floor    6/6/2007
  3. LR3-SG    1545                      2nd Floor    6/6/2007
  4.  
May 31 '07 #5

Rabbit
Expert Mod 10K+
P: 12,441
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
  1. Job    Model    JobReference    Floor    DesSchDate
  2. LR3-SG    1545                      1st Floor    6/6/2007
  3. LR3-SG    1545                      2nd Floor    6/6/2007
  4.  
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.
May 31 '07 #6

P: 34
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.
Jun 1 '07 #7

Rabbit
Expert Mod 10K+
P: 12,441
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.
Jun 1 '07 #8

Rabbit
Expert Mod 10K+
P: 12,441
In this case, instead of TOP, use the aggregate Max or Min, depending on what you need.
Jun 1 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.