Mysql distinct

Databases
1

Hello all,

I seem to be having a problem and am not sure if I am applying this properly.
Some users have registered using the same email as their relatives, so I want to eliminate the duplicate records using distinct, while at the same time I want to select records between x and y.

I tried this and still saw some duplicate emails.


$result1 = mysql_query("select (DISTINCT email), bid, fname, lname, email 
from $table_name 
WHERE bid BETWEEN 543 AND $num_rows 
group by email") 
or exit ();

What am I doing wrong or is there another way of doing this?

2

I found a problem when doing this.

Duplicates are still sent using the method below because the group by email is only being applied to the set of records requested.

It only works when not using WHERE bid BETWEEN 543 AND $num_rows .

The way to solve this would be to create a temporary table using group by email and then to apply the where to that table.

I will create an alternative approach at another time.

3 

SELECT
    email
  , bid
  , fname
  , lname
FROM $table_name
WHERE bid BETWEEN 543 AND $num_rows
GROUP BY email

and


SELECT DISTINCT
    email
  , bid
  , fname
  , lname
FROM $table_name
WHERE bid BETWEEN 543 AND $num_rows

do not give the same number of rows, unless all values in all columns (bid, fname and lname) are identical in all rows with the same email. Otherwise, DISTINCT will give more rows than GROUP BY.

4

Hello guido2004,

I just tried that and it appears to work.

That makes me wonder then, how does that differ from using distinct?

I tried using both ways and they both appear to give me the same number of records but when I used it as in my first post, it allowed duplicate email addresses to be shown.

5

Maybe you posted the wrong link? I don’t see it used this way?

I don’t need the application of other functions against the other fields.
bid is an id field and I simply want to have only unique email addresses for a certain number of records - bid.

If you want each email address only once, and you don’t care about the value of the other columns (since you’ll have only one row per email, you will have only one value for all other columns too), you can do this:


SELECT 
    email
  , bid
  , fname
  , lname
FROM $table_name 
WHERE bid BETWEEN 543 AND $num_rows 
GROUP BY email
6

Hello r937,

Yes I am aware that it is not a function but the mysql reference manual shows it being used in this way.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

Sorry, I didn’t see the forum for mysql.

I don’t need the application of other functions against the other fields.
bid is an id field and I simply want to have only unique email addresses for a certain number of records - bid.

If anyone knows of another way of doing this it would be great.

Thanks

7

DISTINCT is ~not~ a function, and it applies to all columns in the SELECT clause

if you want only one row per email, you are correct to use GROUP BY, but you will have to apply aggregate functions to the other columns in the SELECT clause, something like this –

SELECT email
     , [COLOR="Red"]AVG(bid)[/COLOR]    AS bid
     , [COLOR="red"]MAX(fname)[/COLOR]   AS fname
     , [COLOR="red"]MIN(lname)[/COLOR]   AS lname
  FROM $table_name 
 WHERE bid BETWEEN 543 AND $num_rows 
GROUP 
    BY email

p.s. please use the mysql forum in future :slight_smile:

8

Thank you for the clarification.

9

What?