On 4/20/02 6:10 PM, "[EMAIL PROTECTED]"
> From: "Jeff Oien" <[EMAIL PROTECTED]>
> Subject: DISTINCT Question
> How can I do a query for a distinct field plus get the info from
> other fields? 

DISTINCT and GROUP BY are your two choices.  But they have particular rules

>I tried this:
> select DISTINCT email, name_first, name_last, id from applicants;
> and it gave me everything.

I imagine this is because you mean for "DISTINCT" to only apply to the
column email but it has to apply to all of the columns so you may have 20
rows with the email "[EMAIL PROTECTED]" and if the other columns are different you
will get that row.   It has to work that way - think about it, if it would
only apply to one column which of the other 20 rows would it use for the
rest of the columns in your select?

GROUP BY also has a similar restriction in that every column in your select
must either be in the GROUP BY clause (meaning you'd still have the same
problem you've got now since a difference in any one of those columns will
return that whole row) or GROUP BY does allow you to use `aggregate`
functions on the other columns which can be useful but be careful here too.

If you had 5 rows 

email,      name_first, name_last, id
[EMAIL PROTECTED]  f           foo        1
[EMAIL PROTECTED]  f           foo        2
[EMAIL PROTECTED]  f           foo        3
[EMAIL PROTECTED]  fred        foo        4
[EMAIL PROTECTED]  foo         foo        5

With DISTINCT you're gueranteed to get all the rows since the id is unique.

Now you can try:

select email, name_first, name_last, id
from applicants
  GROUP BY email

But this will fail because you must tell the DB what to display for the
other columns.  It will have the values from the 5 matching rows for each
columns so you can say:

Count(*) /* doesn't require a column*/
Count(<column name>)
Count(distinct(column name))

Maybe there are others - check the documentation under aggregate functions

You could do:

select email, max(name_first), max(name_last), max(id)
from applicants
  GROUP BY email

And this would return:

 [EMAIL PROTECTED]  f           foo        5

BUT THIS ISN'T A ROW THAT EXISTS!!!  Row id=5 is different.

I'd go back to your design and try to figure out why you're recording
duplicate data - perhaps that's a better way to fix your problem.

Good Luck,

> Thanks.
> Jeff Oien

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to