Cameron,
#temp is your table name:
select
t1.*
from
#temp t1 join
(
select t1.email, t1.rank
from
#temp t1
where
not exists (
select 1
from #temp t2
where
t1.email = t2.email
and t1.rank > t2.rank
)
) t2 on
t1.email=t2.email
and t1.rank=t2.rank
Vygandas Razhas
-----Original Message-----
From: Cameron Childress [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 24, 2001 6:52 AM
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
