SELECT DISTINCT statement

[gelboe]

I have a working select distinct statement that returns a recordset to ASP containing all records from my table, each record with a unique "email" value

"SELECT DISTINCT email from myTable"

However in addition the email address I also want all other columns of data in my recordset i.e. dob, gender etc but the statement above only selects the "email" column data.

Can anyone help me ?

thanks in advance

gelboe

 

[lespaul]

SELECT DISTINCT * FROM myTable

or

SELECT DISTINCT email, dob, gender, etc FROM myTable

realize though that if you have the following data:

  Name       email              dob      gender
Joe Blow    jblow@nowhere.com  10/13/67     M     
[b]J. Blow[/b]     jblow@nowhere.com  10/13/67     M     
Joe Blow    jblow@nowhere.com  [b]11/13/67[/b]     M
Joe Blow    jblow@nowhere.com  10/13/67     [b]F[/b]

the queries above will return all of these records because each one IS DISTINCT

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[gelboe]

thanks

but i have already tried these methods, they work as you explain above but this is not the result i want. I want to select * columns but only under the condition of "email" being DISTINCT

I have looked high and low and dont think this can be done, any further thoughts?

thanks again

gelboe

 

[PHV]

Hopefully your table has a Primary Key:
SELECT A.*
FROM yourTable A INNER JOIN (
SELECT email, First(PKfield) AS Ref FROM yourTable GROUP BY email
) AS B ON A.PKfield = B.Ref;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Here's the issue using Leslie's (slightly modified) example
[tt]
Name email dob gender
Joe Blow jblow@nowhere.com 10/13/67 M
J. Blow jblow@nowhere.com 10/14/67 M
Joseph Blow jblow@nowhere.com 11/15/67 M
JJ Blow jblow@nowhere.com 10/16/67 F
[/tt]
Now, given that you have the same "email" value for all four records, which of the other fields do you want to see reported? As Leslie pointed out, each full record is distinct so you will see all four as DISTINCT records. PHV's suggestion will give you only one record for each email but you will see only the FIRST value of the primary field associated with that email. The others will not appear (and similar comments apply if you use MIN, MAX or LAST aggregate functions.)

In short, SQL has the same problem that you would have doing this manually. You can either show all records because they are all different or you can decide how to restrict the values other than email so that an email value appears only once but some values in other fields don't appear because that would cause duplication of the email value.

 

[r937]

golom, nice examples

i would use GROUP BY with MAX(name), MIN(gender), and AVG(dob)



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)