CODEWITHSUNDEEP
sqlsql-serverdatabase
Stampede10343
Stampede10343

Reputation: 854

SQL Remove Duplicates, save lowest of certain column

I've been looking for an answer to this but couldn't find anything the same as this particular situation.

So I have a one table that I want to remove duplicates from. 

    __________________
   | JobNumber-String |
   | JobOp -   Number |
    ------------------

So there are multiples of these two values, together they make the key for the row. I want keep all distinct job numbers with the lowest job op. How can I do this? I've tried a bunch of things, mainly trying the min function, but that only seems to work on the entire table not just the JobNumber sets. Thanks!

Upvotes: 3

Views: 2286

Answers (5)

Doolius
Doolius

Reputation: 874

Original Table Values:

JobNumber   Jobop
123          100
123          101
456          200
456          201
780          300

Code Ran:

DELETE FROM table
WHERE CONCAT(JobNumber,JobOp) NOT IN 
    (
    SELECT CONCAT(JobNumber,MIN(JobOp))
    FROM table
    GROUP BY JobNumber
    )

Ending Table Values:

JobNumber   Jobop
123          100
456          200
780          300

Upvotes: 2

ChipsLetten
ChipsLetten

Reputation: 2953

It sounds like you are not using the correct GROUP BY clause when using the MIN function. This sql should give you the minimum JobOp value for each JobNumber:

SELECT JobNumber, MIN(JobOp) FROM test.so_test GROUP BY JobNumber;

Using this in a subquery, along with CONCAT (this is from MySQL, SQL Server might use different function) because both fields form your key, gives you this sql:

SELECT * FROM so_test WHERE CONCAT(JobNumber,JobOp) 
NOT IN (SELECT CONCAT(JobNumber,MIN(JobOp)) FROM test.so_test GROUP BY JobNumber);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I like to do this with window functions:

with todelete as (
      select t.*, min(jobop) over (partition by numbers) as minjop
      from table t
     )
delete from todelete
    where jobop > minjop;

Upvotes: 0

Luc
Luc

Reputation: 1491

You can simply select the values you want to keep:

select jobOp, min(number) from table group by jobOp

Then you can delete the records you don't want:

DELETE t FROM table t 
left JOIN (select jobOp, min(number) as minnumber from table group by jobOp )  e
 ON t.jobob = e.jobob and t.number = e.minnumber
Where e.jobob is null

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

With SQL Server 2008 or higher you can enhance the MIN function with an OVER clause specifying a PARTITION BY section.

Please have a look at https://msdn.microsoft.com/en-us/library/ms189461.aspx

Upvotes: 0

Related Questions