Duplicate Rows HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicate Rows HELP

Author

Topic

westerncj
Starting Member

3 Posts

Posted - 2012-06-19 : 14:32:16

Hello. I'm working on query but can't seem to get it to do what I want. On the results below I have duplicate PalletIDs. I want to only show this once. I plan on removing the Order number since it's the only unique field in the row. How can I do this?

Here are my results:

item OrderNo RequiredOn Allocated PalletID PalletQty
--------------- -------- ----------------------- ----------- -------- -----------
08W0038020 75751910 2012-06-18 00:00:00.000 20 818560OP 20
08W0038020 76130940 2012-06-19 00:00:00.000 20 818560OP 20
08W0038020 75751910 2012-06-18 00:00:00.000 20 819032OP 20
08W0038020 76130940 2012-06-19 00:00:00.000 20 819032OP 20

I want to see.......

08W0038020 75751910 2012-06-18 20 818560OP 20
08W0038020 75751910 2012-06-18 20 819032OP 20

Here is my query.. I tried Distinct but it didn't work. Can anyone help??



SELECT DISTINCT
        o.item
      , o.OrderNo
      , o.RequiredOn
      , o.Allocated
      , d.PalletID
      , d.ParentQty AS PalletQty
FROM    dbo.OrderAlloc o
        JOIN dbo.PalletDetail d ON SUBSTRING(o.item, 3, LEN(o.item)) = d.ParentBaseItem
WHERE   Department = 'FINMIL'
        AND o.Item = '08W0038020'
GROUP BY o.OrderNo
      , o.RequiredOn
      , o.Item
      , d.PalletID
      , o.Allocated
      , d.ParentQty
ORDER BY d.PalletID

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-19 : 14:39:39

One way would be to use an aggregate function on all the columns other than PalletID. Alternatively, you can use ROW_NUMBER() function with a PARTITION BY PalletID clause. For the aggregate function method, the code would be like this:

SELECT  MIN(o.item) AS item
      , MIN(o.OrderNo) AS OrderNo
      , MIN(o.RequiredOn) AS RequiredOn
      , MIN(o.Allocated) AS Allocated
      , d.PalletID
      , MIN(d.ParentQty) AS PalletQty
FROM    dbo.OrderAlloc o
        JOIN dbo.PalletDetail d ON SUBSTRING(o.item, 3, LEN(o.item)) = d.ParentBaseItem
WHERE   Department = 'FINMIL'
        AND o.Item = '08W0038020'
GROUP BY d.PalletID
ORDER BY d.PalletID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 15:07:37

there's a potential issue with Sunitas suggestion above. It will not necessary guarantee all values are from same row if it matters!. so you may be better off using derived table inside which you apply aggregation based logic like


SELECT 
        o.item
      , o.OrderNo
      , o.RequiredOn
      , o.Allocated
      , d.PalletID
      , d.ParentQty AS PalletQty
INTO #Temp
FROM    dbo.OrderAlloc o
        JOIN dbo.PalletDetail d ON SUBSTRING(o.item, 3, LEN(o.item)) = d.ParentBaseItem
WHERE   Department = 'FINMIL'
        AND o.Item = '08W0038020'
GROUP BY o.OrderNo
      , o.RequiredOn
      , o.Item
      , d.PalletID
      , o.Allocated
      , d.ParentQty



SELECT t.*
FROM #Temp t
INNER JOIN (SELECT PalletID,MIN(OrderNo) AS MinOrder
            FROM #Temp
            GROUP BY PalletID)t1
ON t1.PalletID = t.PalletID 
AND t1.MinOrder = t.OrderNo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-19 : 15:56:24

quote:
there's a potential issue with Sunitas suggestion above. It will not necessary guarantee all values are from same row if it matters!. so you may be better off using derived table inside which you apply aggregation based logic like

Very true, Visakh! Thanks for pointing it out.

westerncj
Starting Member

3 Posts

Posted - 2012-06-19 : 16:08:37

Thanks guys! All my order numbers are the same now per item.. but not a big deal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 18:54:26

quote:
Originally posted by sunitabeck

quote:
there's a potential issue with Sunitas suggestion above. It will not necessary guarantee all values are from same row if it matters!. so you may be better off using derived table inside which you apply aggregation based logic like

Very true, Visakh! Thanks for pointing it out.

No problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources