Earliest Start for Multiple Distinct Records

Ok, I am not sure if I worded the title correctly.  I have looked at numerous examples on here and on SO, but nothing seems to work as they all have 'Where' clauses that I can't get to work.  

I have a table that has a date/time column.  I want to get the earliest time for each distinct item in another column.  Here, take a look at my data.

SQL Table
'Machine' is my distinct column.  You can see the highlighted records that I want it to return.  I want to get the orderNumber, Tool, and Color for each of the earliest dates.  I have tried numerous things, but nothing seems to really work.  Thank you for any help you can give me on this - it has been driving me crazy for days.
LVL 1
G ScottAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

G ScottAuthor Commented:
SELECT        *
FROM            (SELECT        *, ROW_NUMBER() OVER (PARTITION BY masch_nr
                          ORDER BY fulltime) rn
FROM            u_order_start_times) q
WHERE        rn = 1


That did it.  I will leave it here to help others.
0
Kevin CrossChief Technology OfficerCommented:
I am glad you figured that out.  If you select your comment above as the solution, it will close the question (so the Experts understand you solved the problem); however, it will leave the topic available to help others.

With that in mind, here is a link for future readers that may help understand the solution.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1555-Analytical-SQL-Where-do-you-rank.html
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I want to get the orderNumber, Tool, and Color for each of the earliest dates.
There's an article called SQL Server GROUP BY Solutions, where point #5 'Aggregate AND values from a single row that make up the aggregate:  Subquery' is a demo of exactly what you're trying to pull off.
0
Anthony PerkinsCommented:
That did it.  I will leave it here to help others.
Ouch!  You may want to look into using a CTE in this case.  You may find that a tad more efficient and easier to read.
0
David ToddSenior DBACommented:
Hi,

select
    orderNumber, machine, Tool, Color , min( StartTime ) as EarliestDate
from dbo.u_order_start_times
group by
    orderNumber, machine, Tool, Color
;

HTH
  David
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
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 SQL Server 2008

From novice to tech pro — start learning today.