Link to home
Start Free TrialLog in
Avatar of Robert Francis
Robert Francis

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?
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

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...
Avatar of Robert Francis
Robert Francis

ASKER

Does this work with SQL Server 2008?
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.
Avatar of 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
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.