Select first occurrence of record in SQL Server

Robert Francis
Robert Francis used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
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...
Robert FrancisDirector of Continuous Improvement

Author

Commented:
Does this work with SQL Server 2008?
Mike EghtebasDatabase and Application Developer

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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 ...
Database and Application Developer
Commented:
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

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial