[RESOLVED] how do I find cols with more than 1 of a value in it ?

steamPunk

I have a table which may have some duplicate values in one column - i'm going to make it a unique col but first i need to look at any duplicates

I've tried this

SELECT * FROM `pl` WHERE count(`PL_ID_SITE`)>1 GROUP BY `PL_ID_SITE`

but i'm getting an error :

#1111 - Invalid use of group function

so I'm obviously not doing it right -

how would i need to write the query so that it just returns rows where there are 2 or more rows with the same value in the PL_ID_SITEcolumn ?

thanks

laserlight

I tend to forget the exact syntax, but it could be along the lines of:

SELECT * FROM `pl` GROUP BY `PL_ID_SITE` HAVING count(`PL_ID_SITE`) > 1

steamPunk

thanks for your reply

that kind of works, but not completely, because, although it finds an example of a row with a value that duplicates a value in the same col in a different row, it only returns 1 row as a result whereas it should return 2 rows, as there are 2 rows with the same value

my uneducated guess is that it's the GROUP BY that's aggregating the 2 rows into 1 result - i'm trying to think of a way round it

laserlight

Just omit the GROUP BY clause?

steamPunk

hm no ....

if i do

SELECT * FROM pl HAVING count(PL_ID_SITE) > 1

it just returns the first row of the table

as there are only 385 rows it may be quicker just to order them by the PL_ID_SITE column and check through manually 🙂

Installer

This doesn't seem ideal, but it should work:

$query = "SELECT * FROM p1 WHERE my_col NOT IN (SELECT DISTINCT my_col FROM p1)";

Installer

I should have tested it first; doesn't work.

laserlight

steamPunk wrote:
it just returns the first row of the table

Right. I wonder how I managed to score reasonably well for my database module in university :o

Installer wrote:
This doesn't seem ideal, but it should work:

The problem is that you are selecting all the rows for which my_col does not have a value. I suppose that will either return the empty set, or maybe also those rows in which my_col is NULL.

However, that is easily fixed:

SELECT * FROM pl WHERE PL_ID_SITE NOT IN (SELECT PL_ID_SITE FROM p1 GROUP BY PL_ID_SITE HAVING COUNT(PL_ID_SITE) = 1)

There should be an even better solution though 🙁

steamPunk

you got it !

this one works fine

 SELECT *
FROM `pl`
WHERE PL_ID_SITE NOT
IN (
SELECT PL_ID_SITE
FROM pl
GROUP BY PL_ID_SITE
HAVING COUNT( PL_ID_SITE ) =1
)
LIMIT 0 , 30

I've seen that SELECT within a SELECT before but I've never really examined how that works - i guess that the time to look at it has arrived

thanks for that 🙂

Installer

The problem is that you are selecting all the rows for which my_col does not have a value. I suppose that will either return the empty set, or maybe also those rows in which my_col is NULL.

Yeah. the subquery returns one row for each unique value in my_col. I was thinking it (the subquery) would return a row just for each value that isn't duplicated.

SELECT within a SELECT

That's part of what SQL was designed for, so as to get rid of joins, or at least lessen dependence on them.

steamPunk

With each project I do I learn more about how SQL works but I still get the feeling that it's just the tip of the iceberg !

thanks for your help