Select, Distinct & Duplication

[ReeceSayer]

Hi,

 

I'm trying to remove duplicates from my database table (vacancy).

 

Currently using the following SQL statement:

 

SELECT title, jobref, location, description, salary, apply FROM vacancy WHERE jobref IN (SELECT DISTINCT jobref FROM vacancy)

 

However, this still brings back the duplicates, each statement works on its own (I.e. DISTINCT brings back unique values for that column, and SELECT * brings back all rows & Columns) so i'm not entirely sure why it's not working.

 

If there's a simpler way of doing this i'm open to trying it out.

 

Cheers

[Barand]

This will get the details of those records with duplicated jobrefs (if that is what you are after)

 

SELECT title, jobref, location, description, salary, apply
FROM vacancy
   INNER JOIN (
    SELECT jobref, COUNT(*) as tot
    FROM vacancy
    GROUP BY jobref
    HAVING tot > 1
   )  as x USING (jobref)
ORDER BY jobref

[ReeceSayer]

This will get the details of those records with duplicated jobrefs (if that is what you are after)

 

SELECT title, jobref, location, description, salary, apply
FROM vacancy
INNER JOIN (
 SELECT jobref, COUNT(*) as tot
 FROM vacancy
 GROUP BY jobref
 HAVING tot > 1
) as x USING (jobref)
ORDER BY jobref

 

Thanks works brilliantly, I've just used IGNORE when importing the data and set the jobrefs column to unique which also seems to do the job (i'm importing a different data-set every day and didn't want duplicates).

 

Thanks again