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)) Sum(col) Ave(col) Max(col) Min(col) 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, Frank > Thanks. > Jeff Oien > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
