Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Earliest Start for Multiple Distinct Records

Posted on 2014-03-03
5
Medium Priority
?
165 Views
Last Modified: 2014-05-02
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.
0
Comment
Question by:G Scott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 1

Author Comment

by:G Scott
ID: 39900250
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39900293
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39900408
>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39902306
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
 
LVL 35

Accepted Solution

by:
David Todd earned 1500 total points
ID: 39902417
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question