Removing Duplicate Rows in SQL

Question

I have a query that returns a list of devices that have multiple "moved" dates. I only want the oldest date entry. I used the MIN function to give me the oldest date, but I'm still getting multiple entries (I am, however, getting less than before). I tried to get a more precise JOIN, but I couldn't narrow the fields down any more.

If you'll look at the screenshot, the first three rows have the same "wonum" but three different "Moved Dates." I am thinking that if I can somehow take the oldest "Moved Date" out of those three and remove the other rows, that would give me the result I'm looking for. However, I'm not skilled enough to do that (I've only been working in SQL for a few months now). Would that work, or is there a better way to narrow down my results? I'm wondering if I need to perform some kind of sub-query to get what I need.

I've looked around but can't find anything that allows me to remove a row of data the way I'm looking to. Nor can I seem to find a reason my MIN function isn't paring down the data anymore than it is. Below is the code I'm currently using. Thanks for any help that can be given.

SELECT wo.wonum, wo.location, wo.statusdate, wo.status, l.subcontractor,
    wo.description, MIN(ast.datemoved) AS 'Moved Date'
FROM workorder wo
        JOIN locations l ON wo.location = l.location
        JOIN asset a ON wo.location = a.location 
          -- AND wo.assetnum = a.assetnum
        JOIN assettrans ast ON a.assetnum = ast.assetnum
          -- AND a.assetid = ast.assetid
WHERE wo.description LIKE '%deteriorating%'
        AND wo.status != 'close'
GROUP BY wo.wonum, wo.location, wo.statusdate, 
  wo.status, l.subcontractor, wo.description
ORDER BY wo.wonum;

DBV SQL Query Result

Update: Table Data

Can you post the results of the query? If you group by the columns listed, then you won't have duplicate rows across those 7 columns. — Kirk Broadhurst, Jun 01 '18 at 20:49
From his data set shown in the picture he has multiple columns which are not identical, so grouping by just does nothing. — Doug Coats, Jun 01 '18 at 20:50
To be helpful we might need to know more about the other tables in this join . Odds are you'll have to use a subquery to get the min value seperately form the rest of the data. Are there any other unique identifiers in the assettrans table? — Doug Coats, Jun 01 '18 at 20:52
I have scoured the other tables and have joined on any I can find. I actually don't need "assetnum" table; I only used it to have a way to join the "assetid" table to the query. It seems to have a lot of columns that don't cross into any other tables I'm using. — TaRan, Jun 01 '18 at 20:58
@TaRan, if you want help provide the table layout of each table, at least to the point of showing the columns that you are using. If you are using data from one table that's also in another show that too — DCR, Jun 01 '18 at 21:00
@DCR I updated the original post with an image of the table layout of the tables I'm using in the query. I hope this is what you're looking for. I do not have access to server side, so I had to download a tool (dbForge) to help me create it - I'm not sure if I did it correctly or not. If you're looking for something other than this, please let me know. — TaRan, Jun 04 '18 at 19:11
@TaRan, go here http://sqlfiddle.com/ and set up your tables then provide a link back to your fiddle. show us what output you want — DCR, Jun 05 '18 at 01:41

JanneP · Accepted Answer · 2018-06-05T07:56:51.650

0

You need to do the grouping in your join statement inside a subquery(not tested, but you'll get the idea):

Replace

JOIN assettrans ast ON a.assetnum = ast.assetnum

With

inner join
(
    select ast.assetnum,MIN(ast.datemoved) AS 'Moved Date' 
    from assettrans ast  
    group by ast.assetnum
) grouped
on a.assetnum = grouped.assetnum

So the full query looks like:

SELECT wo.wonum, wo.location, wo.statusdate, wo.status, l.subcontractor,
    wo.description, grouped.MovedDate
FROM workorder wo
        JOIN locations l ON wo.location = l.location
        JOIN asset a ON wo.location = a.location 
        INNER JOIN
        (
           select ast.assetnum,MIN(ast.datemoved) AS MovedDate 
           from assettrans ast  
           group by ast.assetnum
        ) grouped
        on a.assetnum = grouped.assetnum
    WHERE wo.description LIKE '%deteriorating%'
            AND wo.status != 'close'
    ORDER BY wo.wonum;

edited Jun 05 '18 at 07:56

answered Jun 01 '18 at 21:24

JanneP

577
4
12

I tried your solution, but I am getting the same result as I do when I don't use a subquery; I replaced my "assettrans" join with the code you provided. I tried moving it around within the original query and did some small tweaks, but I always seem to get the same result. I updated my original post with a new image as requested by another user. Maybe it'll help. – TaRan Jun 04 '18 at 19:16
Please see my edited post for full sql sample. Haven't tested it but it should be somewhat correct. – JanneP Jun 05 '18 at 07:58
Thanks for the help. It looks like I was missing "group.MovedDate" in my select statement, which is why the subquery wasn't working for me. Thanks for helping me get this sorted out. This helped me with learning subqueries. :) – TaRan Jun 05 '18 at 20:33

score 0 · Answer 2 · answered Jun 01 '18 at 21:34

0

Please test before using in production

--if you have id column and leave the oldest record

delete from T1 from MyTable T1, MyTable T2 where T1.dupField = T2.dupField (and add more filters if applies) and T1.uniqueField > T2.uniqueField

--if you want to delete the new "Moved Dates" and leave the oldest one

delete from T1 from MyTable T1, MyTable T2 where T1.dupField = T2.dupField (and add more filters if applies) and T1.Moved Dates > T2.Moved Dates

answered Jun 01 '18 at 21:34

Zehra Nasif

181
1
5

Is this supposed to be a subquery within my original query? I'm not sure how to use this. It looks like this is deleting entries from the table within the database itself; that is not what I'm looking to do. I am wanting to delete lines from my end query on the database (if that makes sense). – TaRan Jun 04 '18 at 19:21

Removing Duplicate Rows in SQL

2 Answers2