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.

Thanks!
terpsichoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

terpsichoreAuthor Commented:
I'm thinking maybe I can use subquery?
0
mbizupCommented:
You're thinking along the right lines.

Basically start w
0
mbizupCommented:
Sorry.. with a query to pull the max per part and use that in a join to get any other fields needed.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.
0
mbizupCommented:
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
0
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.
0
mbizupCommented:
SELECT * FROM PARTS p
INNER JOIN
(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)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terpsichoreAuthor Commented:
many thanks - i will work on this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.