I try to use the "distinct" statement with no results,
Thanks for any help,

SQL statement used :

select distinct email,nom,prenom,interet,envoyer,langue,site,id from mailing_list

It returns 2 identical records.

I want to filter on "email"
Records with same "email" should be returned only once.

Where is the problem ?

Thanks

Your query is returning all distinct COMBINATIONS of (email,nom,prenom,interet,envoyer,langue,site,id )

It cannot return 2 "identical" records, but it CAN return 2 rows with the same email. Suppose 2 people have the same email address - your query MUST show them both (or what would it show for "nom" etc.?)

To just get the distinct email addresses:

select distinct email from mailing_list

So, I may not understand well the "distinct" statement.
(I do not find any documentation)

I want all the records, but avoid duplication of records which
have the same email.

So, what could be the SQL statement for that.

I thought that,
select distinct email,* from mailing list

will take all records and list records with same email only once,
whatever are the other fields content ?

Thanks for your help,
Pierre.

Tell me what you expect to see if the table contains the following data:

email,nom,prenom,interet,envoyer,langue,site,id
---------------------------------------------------------
abc@xyz.com, Andrews, Tony, xxx, xxx, English, London, 123
abc@xyz.com, Larde, Pierre, yyy, yyy, Francais, Paris, 456

Do you want to see my details, or yours? SQL cannot decide, and it will show both!

You could decide on some arbitrary rule, like show the person with the lowest ID number:

SELECT email,nom,prenom,interet,envoyer,langue,site,id
FROM mailing_list m
WHERE m.id =
( SELECT MIN(m2.id) FROM mailing_list m2 WHERE m2.email = m.email );

Output:

email,nom,prenom,interet,envoyer,langue,site,id
---------------------------------------------------------
abc@xyz.com, Andrews, Tony, xxx, xxx, English, London, 123

But this goes against what you said: "I want all the records..."

You get all the email values, but NOT all the records.

Not Simple apparently..

My fields are :
email,nom,prenom,interet,envoyer,langue,site,id

I want all records, but if 2 or more records have the same email, I only want the one which have a value of 0 for the site .

Thanks for your help,

Do you know a good adress for SQL documentation ?
(syntax, usage, examples).

Pierre.

If 0 is the lowest site value then you could perhaps use:

SELECT email,nom,prenom,interet,envoyer,langue,site,id
FROM mailing_list m
WHERE m.site =
( SELECT MIN(m2.site) FROM mailing_list m2 WHERE m2.email = m.email );

Otherwise it is more tricky. Something like:

SELECT email,nom,prenom,interet,envoyer,langue,site,id
FROM mailing_list m
WHERE 1 =
( SELECT COUNT(*) FROM mailing_list m2 WHERE m2.email = m.email );
UNION
SELECT email,nom,prenom,interet,envoyer,langue,site,id
FROM mailing_list m
WHERE site = 0
AND 1 <
( SELECT COUNT(*) FROM mailing_list m2 WHERE m2.email = m.email );

But this seems dodgy. What if there are 2 records for an email, and NEITHER has site = 0?

I can't recommend any general SQL documentation. I can point you to Oracle's or SQL Server's on-line documentation, but they are only relevant to those databases of course.

Thank you, I will try.

So, "distinct" does not apply on a field, but on a
full record. Means that all fields should be equal to make
a distinct ?
So if records have an incremental ID , distinct will never
be usefull ?

Thanks again.

Pierre.

Yes, DISTINCT works on all the fields that you select in the statement. It is only useful where the query may return duplicate RECORDS.

"So if records have an incremental ID, distinct will never be useful?"

on the contrary, DISTINCT is quite useful, although you are probably thinking that it might not be useful if the incremental ID is one of the SELECT DISTINCT columns, and you're right

but it is easy to imagine a table of employees, each with a unique ID, and yet still wanting to run a DISTINCT query

for example, what countries do our employees live in?

select distinct country from employees


rudy

Is there anything that might work along these lines?

SELECT (DISTINCT Field1) as Field1, (DISTINCT Field2) as Field2

no, w0rldd0m, it doesn't work that way

DISTINCT is not really a function, as you have coded it

if your rows are

1 X
1 Y
2 A
2 B
2 X
3 A
3 Y
4 Z

then what would you like to come back with?

the distinct first column values are 1,2,3,4, and the distinct second column values are A,B,X,Y,Z

but how exactly do you want to show them combined?

what would your result set look like?

I need these fields:

a. modelNumber
b. Manufacturer
c. LaborRate
d. ProductDescription

I need the records where a,b, and c are unique together
AND
when that condition exists append d.

if there are multiple a,b,c rows, i.e. different values of d, then which d would you like to pick for the single result row? the biggest? the smallest?

'd' should not be distinct in the select clause. so a,b, and c are distinct but d is not.

would a union work?

no, a union isn't what you're after

you said "a,b,c are distinct and d is not"

okay, let's take an example

a1 b4 c6 d12
a1 b4 c6 d13
a1 b4 c6 d14
a1 b4 c6 d15
a3 b7 c1 d20
a3 b7 c1 d21
a3 b7 c1 d22
a9 b3 c7 d77


what results do you want from the above?

a1 b4 c6 d??
a3 b7 c1 d??
a9 b3 c7 d77