Removing Duplicate row from a table based on one column

Question

I created a table with multiple inner joins from 4 tables but the results brings back duplicate records. Here code that I am using

SELECT   tblLoadStop.LoadID,
         tblCustomer.CustomerID,
         tblLoadMaster.BillingID,
         tblLoadMaster.LoadID,
         tblLoadMaster.PayBetween1,
         LoadStopID,
         tblLoadMaster.Paybetween2,
         tblStopLocation.StopLocationID,
         tblStopLocation.city,
         tblStopLocation.state,
         tblStopLocation.zipcode,
         tblLoadSpecifications.LoadID,
         tblLoadSpecifications.LoadSpecificationID,
         Picks,
         Stops,
         Typeofshipment,
         Weight,
         LoadSpecClass,
         Miles,
         CommodityList,
         OriginationCity,
         OriginationState,
         DestinationCity,
         DestinationState,
         LoadRate,
         Status,
         CompanyName,
         Customerflag,
         tblCustomer.CustomerID,
         tblCustomer.AddressLine1,
         tblCustomer.City,
         tblCustomer.State,
         tblCustomer.Zipcode,
         CompanyPhoneNumber,
         CompanyFaxNumber,
         SCAC,
         tblLoadMaster.Salesperson,
         Change,
         StopType
FROM     tblLoadMaster
            INNER JOIN tblLoadSpecifications 
                  ON tblLoadSpecifications.LoadID = tblLoadMaster.LoadID
            INNER JOIN tblLoadStop
                   ON tblLoadStop.LoadID = tblLoadMaster.LoadID
            INNER JOIN tblStopLocation
                   ON tblStopLocation.StopLocationID = tblLoadStop.StopLocationID
            INNER JOIN tblCustomer
                   ON tblCustomer.CustomerID = tblLoadMaster.CustomerID
WHERE    tblLoadMaster.Phase LIKE '%2%'
ORDER BY tblLoadMaster.LoadID DESC;

This is the result that I get

Load ID   Customer   Salesperson     Origin  Destination     Rate    
-------------------------------------------------------------------------
13356     FedEx           Alex           Duluth    New York     300
13356     FedEx           Steve          Florida   Kansas       400

I only want the first row to show,

13356     FedEx           Alex           Duluth    New York     300

and remove the bottom row,

13356     FedEx           Steve          Florida   Kansas       400

The tblLoadStop Table has the duplicate record with a duplicate LoadID from tblloadMaster Table

which table has duplicate records? Filter duplicates out of that table — Tilak, Jan 06 '13 at 15:49
As @Tilak says, try adding a group by tblLoadStop.LoadID on the first select. — Giorgio Minardi, Jan 06 '13 at 15:52
How do you define which row is the **first** row, the one to keep? Based on what column ordering? — marc_s, Jan 06 '13 at 15:53
See possible duplicate http://stackoverflow.com/questions/966176/select-distinct-on-one-column — E.T., Jan 06 '13 at 15:59
the duplicate record is in the tblLoadstop Table with duplicate LoadID and I will like to order by the tblLoadstopID in ASC order with the first row in the table being used. — Alex, Jan 06 '13 at 16:17
giorgio-minardi, can you give me an example of how to do the groupby process. — Alex, Jan 06 '13 at 16:27

marc_s · Answer 1 · 2013-01-06T18:22:53.713

One approach would be to use a CTE (Common Table Expression) if you're on SQL Server 2005 and newer (you aren't specific enough in that regard).

With this CTE, you can partition your data by some criteria - i.e. your LoadID - and have SQL Server number all your rows starting at 1 for each of those "partitions", ordered by some criteria (you're not very clear on how you decide which row to keep and which to ignore in your question).

So try something like this:

;WITH CTE AS
(
   SELECT 
       LoadID, Customer, Salesperson, Origin, Destination, Rate,
       RowNum = ROW_NUMBER() OVER(PARTITION BY LoadID ORDER BY tblLoadstopID ASC) 
   FROM 
       dbo.tblLoadMaster lm
     ......
   WHERE
      lm.Phase LIKE '%2%'
)
SELECT 
   LoadID, Customer, Salesperson, Origin, Destination, Rate
FROM 
   CTE
WHERE
   RowNum = 1

Here, I am selecting only the "first" entry for each "partition" (i.e. for each LoadId) - ordered by some criteria (updated: order by tblLoadstopID - as you mentioned) you need to define in your CTE.

Does that approach what you're looking for??

@Alex: updated my response, although I'm not entirely sure I got that column right .... — marc_s, Jan 06 '13 at 18:23
@Alex: there is **no comma** between `PARTITION BY` and `ORDER BY` in the `OVER(....)` clause of `ROW_NUMBER()` ..... — marc_s, Jan 07 '13 at 05:47

Removing Duplicate row from a table based on one column

1 Answers1

Linked

Related