asked on

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?

Mike Eghtebas

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 Francis

ASKER

Does this work with SQL Server 2008?

Mike Eghtebas

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.

Andrei Fomitchev

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

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

PortletPaul

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.