Select first occurrence of record in SQL Server

So I got the first part of this problem done fairly easily. Here are a few rows of the 96,000 rows I am dealing with.

Part Number / Order Date / Customer / Prince / Top Lvl Job / Job
3418-7      2013-09-23 00:00:00.000      SIMMONS      254.8000      57207      57207
3418-B      2012-01-11 00:00:00.000      SIMMONS      194.5100      36608      36608
3418-B      2013-04-24 00:00:00.000      SIMMONS      183.5000      50890      50890
3418-BB      2013-04-17 00:00:00.000      SIMMONS      60.0000      50695      50695
3418-BB      2010-01-15 00:00:00.000      SIMMONS      282.5900      21685      21685
3418-BB      2015-05-27 00:00:00.000      SIMMONS      182.5000      81747      81747
3418-BB      2014-08-21 00:00:00.000      SIMMONS      273.7500      70911      70911
34182-C      2009-07-24 00:00:00.000      ENERCON      863.0000      19440      19440
3418BB      2013-04-24 00:00:00.000      SIMMONS      190.8400      50889      50889
3418BB      2012-05-10 00:00:00.000      SIMMONS      194.5100      40080      40080
3419      2012-08-07 00:00:00.000      SIMMONS      361.5300      42860      42860
3419      2013-02-11 00:00:00.000      SIMMONS      537.0300      48577      48577
3419      2013-02-22 00:00:00.000      SIMMONS      351.0000      48951      48951
3419      2012-07-18 00:00:00.000      SIMMONS      358.0200      42300      42300
3419      2010-01-05 00:00:00.000      SIMMONS      709.0200      21544      21544
3419      2009-06-08 00:00:00.000      SIMMONS      560.0000      18807      18807
3419      2010-05-19 00:00:00.000      SIMMONS      702.0000      23450      23450
3419      2009-10-27 00:00:00.000      SIMMONS      526.5000      20626      20626

What I want is the first occurrence of every part number, which is easy if I use:

SELECT     Part_Number, MIN(Order_Date) AS date
FROM         dbo.Job
GROUP BY Part_Number

Open in new window


This returns 47,000 records which is right. But as I continue to add the remaining columns it doesn't work anymore.

In other words the above code returns a unique record consisting of a part number and the first order date. Now I want to add the customer, price, top lvl job, and job SPECIFIC to the unique record the above code generated. In other words I can't use MIN for the remaining columns which I tried.

For example: The sql statement above would return:

3419      2009-10-27 00:00:00.000

as the unique record for Part Number 3419, but what I want returned is this:

3419      2009-10-27 00:00:00.000      SIMMONS      526.5000      20626      20626

Does this make sense?
Robert FrancisDirector of Continuous ImprovementAsked:
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.

Mike EghtebasDatabase and Application DeveloperCommented:
SELECT [Part Number], [Order Date], [Customer], [Prince], [Top Lvl Job], [Job]
FROM ( Select Row_Number() Over( Partition By [Part Number] Order By [Order Date]) As rn,
  [Part Number], [Order Date], [Customer], [Prince], [Top Lvl Job], [Job]
From dbo.Job) As D
rn = 1;

Open in new window


Modified...
0
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Does this work with SQL Server 2008?
0
Mike EghtebasDatabase and Application DeveloperCommented:
I think it works in 2005 and up.. Please note I made some last minute changes in my last post.

You would find "Subqueries at a glance" useful.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Andrei FomitchevCommented:
SELECT * FROM dbo.Job j
JOIN
(
  SELECT    jj.Part_Number, MIN(jj.Order_Date) AS Order_Date
  FROM         dbo.Job jj
  GROUP BY jj.Part_Number
) t
ON j.Part_Number=t.Part_Number
AND j.Order_Date=t.Order_Date
WHERE ...
ORDER BY ...
0
Mike EghtebasDatabase and Application DeveloperCommented:
for readability, try:    (my previous post was missing WHERE clause)

SELECT D.*
FROM ( Select 
   Row_Number() Over( Partition By [Part Number] Order By [Order Date]) As rn
 , [Part Number]
 , [Order Date] 
 , [Customer]
 , [Prince]
 , [Top Lvl Job]
 , [Job]
From dbo.Job) As D
Where rn = 1
Order By [Customer]
, [Part Number];

Open in new window

1

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
PortletPaulEE Topic AdvisorCommented:
The row_number() solution requires fewer passes of the data

The group by solution is "generic" (i.e. can be used in dbms without row_number) but requires a pass of the data for the group by and then in the outer query.
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 SQL Server

From novice to tech pro — start learning today.