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.
'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.
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.
'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.
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.
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1555-Analytical-SQL-Where-do-you-rank.html
With that in mind, here is a link for future readers that may help understand the solution.
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1555-Analytical-SQL-Where-do-you-rank.html
>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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.