Top 1 query - question (SQL)

Dear experts -
I have a table with multiple deadline records per part.
The structure is like this:
TABLE 1 Project table
TABLE 2 Parts table
TABLE 3 Deadlines table (key field Part_ID - links back to Project_ID in first table).

NOW: i need to create a table with several fields from only the first record for each PROJECT in TABLE 3 when it is sorted in a given order.

Max on a field won't do it, since i need to grab other fields as well.

Do I need 2 queries, or can this be done in a single query?

I want to end up with something like this:

Table1.Project_ID  Table3.deadline  table3.deadlinename
one record for each project.

Who is Participating?
mbizupConnect With a Mentor Commented:
(SELECT PN, Max(Deadline), MilestoneName
FROM PartsRevenue r INNER JOIN Miletones m
ON r.Milestone ID = m.MileStoneID
GROUP BY PN, MilestoneName) q
ON p.PN = q.PN

(again psuedocode - change table/fieldnames as needed)
terpsichoreAuthor Commented:
I'm thinking maybe I can use subquery?
You're thinking along the right lines.

Basically start w
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Sorry.. with a query to pull the max per part and use that in a join to get any other fields needed.
terpsichoreAuthor Commented:
sorry - can you write it out a bit in pseudocode?
I'm trying to find the FIRST (earliest) milestone record in the related table for a given part, and show a few fields from that record for each project.
it would be something like this -- I'd have to see a sample to be more precise I think:

SELECT t.PN, q.MaxDeadline, t.PartDescription, t.Manufacturer etc
From tblParts Inner Join
(SELECT PN, Max(Deadline) AS MaxDeadline
FROM YourTable
Group BY PN) q
ON q.PN = t.PN
terpsichoreAuthor Commented:
thanks so much.
here is a little more specifics:

Table Parts - Part_ID; project_ID
Table Project_Parts_Revenue: part_ID; deadline, milestone_ID (and linkage via pick_table to name of the milestone - we want to also pull the name of the milestone)

We want to sort the second table to date ascending and pull the first record - we don't only want the date, but also the name of that milestone. and then we want this for each project.

Any light you can shed on this is appreciated.
terpsichoreAuthor Commented:
many thanks - i will work on this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.