Selecting the First Record only

[RSchnable]

Hello everyone,

I have a SQL query that I am trying to select only the first record from. The query is:

SELECT ShipDtl_0.JobNum, ShipHead_0.ShipDate, JobAsmbl_0.TLESetupHours, JobAsmbl_0.TLEProdHours, JobAsmbl_0.TLASetupHours, JobAsmbl_0.TLAProdHours, JobAsmbl_0.TLALaborCost, JobAsmbl_0.TLABurdenCost, JobAsmbl_0.TLAMaterialCost, JobAsmbl_0.TLASubcontractCost, JobHead_0.ProdCode
FROM PUB.JobAsmbl JobAsmbl_0, PUB.JobHead JobHead_0, PUB.ShipDtl ShipDtl_0, PUB.ShipHead ShipHead_0
WHERE JobHead_0.Company = JobAsmbl_0.Company AND JobHead_0.JobComplete = JobAsmbl_0.JobComplete AND JobHead_0.JobNum = JobAsmbl_0.JobNum AND ShipDtl_0.Company = JobAsmbl_0.Company AND ShipDtl_0.Company = JobHead_0.Company AND ShipDtl_0.JobNum = JobAsmbl_0.JobNum AND ShipDtl_0.JobNum = JobHead_0.JobNum AND ShipHead_0.Company = JobAsmbl_0.Company AND ShipHead_0.Company = JobHead_0.Company AND ShipHead_0.Company = ShipDtl_0.Company AND ShipHead_0.CustNum = ShipDtl_0.CustNum AND ShipHead_0.PackNum = ShipDtl_0.PackNum
GROUP BY ShipDtl_0.JobNum, ShipHead_0.ShipDate, JobAsmbl_0.TLESetupHours, JobAsmbl_0.TLEProdHours, JobAsmbl_0.TLASetupHours, JobAsmbl_0.TLAProdHours, JobAsmbl_0.TLALaborCost, JobAsmbl_0.TLABurdenCost, JobAsmbl_0.TLAMaterialCost, JobAsmbl_0.TLASubcontractCost, JobHead_0.ProdCode
ORDER BY ShipDtl_0.JobNum

The problem I am having is that there may be multiple ShipDates per JobNum. The date may or may not be the same date, but all the other data is the same, so it is considered a duplicate. I want to select only the first JobNum. Thanks in advance for the help!

 

[bborissov]

What define a record as "First"?
Remember, there is NO guarantee that you could have the same order when you executed the same Query in SQL Sever if you have no ORDER BY clause.


Did you have some Identity field that could be used for this?
Could you post some example data and desired result from it?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RSchnable]

Sure thing.

Ship Date Job Num
1/08/2010 695 1.5 6.56 0.58 5.29 114.64 264.15
1/11/2010 695 1.5 6.56 0.58 5.29 114.64 264.15

As you can see, this record prints twice because there are unique ship dates for it, but all the data that I need is duplicated, so I only need the first instance of this job number.

 

[bborissov]

OK for THAT example data yuo could use:

SELECT *
FROM YourTable
INNER JOIN (SELECT Job, MIN(Date) AS Date
                   FROM YourTable
            --- WHERE if applicable
            GROUP BY Jon) Tbl1
      ON YourTable.Job  = Tb1.Job AND
         YourTable.Date = Tb1.Date
[code]
Nit that exaple is base ONLY of the data you provided and uses the ShipDate field to determinate "First" record. If you have MORE than one records with the same date you again will have more records that you want in the result set.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RSchnable]

I'm not sure how to apply that to my code. The example I gave was just a snippet, but I have more tables and many more fields, as shown in my query above. Would that same principle apply to my code?

 

[bborissov]

If you told me what makes a record "FIRST" in your select

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jenlion]

Can you order by JobNum, ShipDate and select Top 1 ?

 

[markros]

Please see this link which explains different approaches for this very common problem

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related

You may also take a look at the second part of this blog with test cases (the link is at the end of the first blog).

PluralSight Learning Library

 

[RSchnable]

Sorry with the slow response. I got pulled into some other projects. Really, there is no 'first' record per se. Because the ship date is unique, I get double the numbers I need. Whether I use FIRST, TOP 1, or whatever, I just need only one copy of the record. For now, I've removed the ShipDate field, so when I SELECT DISTINCT, I get accurate info. I would like, however, to be able to add the shipdate back in at some point, and still run the report accurately.
Thanks again!

 

[markros]

Did you try to look at the blog I pointed you to? Say, use the simplest row_number() approach for your problem to get the latest ship date record.

PluralSight Learning Library