We help IT Professionals succeed at work.
Get Started

Select first occurrence of record in SQL Server

6,376 Views
Last Modified: 2015-08-17
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?
Comment
Watch Question
Database and Application Developer
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE