Select all but ignore duplicates [duplicate]

Question

I have a mysql table in which one field will hold duplicates. I am trying to select all but ignore all rows where a duplicate exists in this field.

So if for example I have 10 rows in total, and 3 of them have duplicates I like to return 8 rows. The 7 that were unique and 1 of the 3 duplicates.

I have tried distinct and group by without success. They ignore all 3 duplicates.

Here's what I tried:

SELECT *
FROM directory_listings
GROUP BY url
WHERE status = 'approved'
ORDER BY site_name ASC
LIMIT $start, $per_page

and

SELECT * DISTINCT url
FROM directory_listings
WHERE status = 'approved'
ORDER BY site_name ASC
LIMIT $start, $per_page

Answer 1

@AlexW - it's just the column 'url' where there could be a duplicate – Ruf1 9 mins ago

Then your first query will work if you correct the syntax - GROUP BY must follow WHERE (per the docs):

SELECT *
FROM directory_listings
WHERE status = 'approved'
GROUP BY url
ORDER BY site_name ASC

Here's an example of a working query in SQL Fiddle.