> But those two rows *are* distinct!
Yes I know, thus the problem... :)
> SELECT email, min(fname), min(lname), min(userid)
> FROM users
> GROUP BY email
Looks good, but consider these two records
[EMAIL PROTECTED], cam, childress, 2
[EMAIL PROTECTED], albert, reynolds, 7
Would your query return the following?
[EMAIL PROTECTED], albert, childress, 2
Or would it actually do the min operations on each in combination on each
entire individual record? I guess I could test this myself...
-Cameron
--------------------
Cameron Childress
elliptIQ Inc.
p.770.460.1035.232
f.770.460.0963
--
http://www.neighborware.com
America's Leading Community Network Software
> -----Original Message-----
> From: DeVoil, Nick [mailto:[EMAIL PROTECTED]]
> Sent: Friday, August 24, 2001 10:17 AM
> To: SQL
> Subject: RE: Using DISTINCT on a single column of a multi-column select
> st atement
>
>
> Cameron
>
> But those two rows *are* distinct!
>
> You need to specify your reason for preferring one particular
> userid or name over another.
>
> Which userid do you want the query to return?
> Or if the fname or lname was different, which one would you want?
>
> Let's say you always wanted the first numerically/alphabetically,
> then you could say
>
> SELECT email, min(fname), min(lname), min(userid)
> FROM users
> GROUP BY email
>
> Would that be OK?
>
> Nick
>
> -----Original Message-----
> From: Cameron Childress [mailto:[EMAIL PROTECTED]]
> Sent: Friday, August 24, 2001 2:52 PM
> To: SQL
> Subject: Using DISTINCT on a single column of a multi-column select
> statement
>
>
> I want to be able to select a dataset without duplicate email addresses in
> it. Easy enough to say something like the following would work:
>
> SELECT DISTINCT email
> FROM Users
>
> But I also need other columns of each record too, such as first and last
> name, and the PK. IE:
>
> SELECT DISTINCT email, fname, lname, userid
> FROM Users
>
> Problem is that SQL considers the entire set of columns selected when
> looking for dups, therefore it thinks the following two rows are distinct:
>
> [EMAIL PROTECTED], childress, cameron, 34
> [EMAIL PROTECTED], childress, cameron, 465
>
> Now, I could spit the recordset out into CF, and group by the
> email address,
> thus eliminating the dups, but I would very much prefer for the dataset to
> come out of the database query all well formatted and ready to go.
> Optimally I would be able to produce a set or records without duplicate
> emails, and also produce a set of records which only contains dups... In
> other words if a particular email address was duplicate in three
> records, I
> could request either the first occurrence of it, or all the occurrences of
> it after the first.
>
> That make sense? Am I asking too much of my database?
> Suggestions? I have
> a feeling I'm just looking at the problem wrong.
>
> -Cameron
>
> --------------------
> Cameron Childress
> elliptIQ Inc.
> p.770.460.1035.232
> f.770.460.0963
> --
> http://www.neighborware.com
> America's Leading Community Network Software
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
