MySql DISTINCT...

Anon

I HAVE A PROBLEM WITH "DISTINCT" MySql COMMAND.
IT DOESN'T WORK!!

EG:
SELECT DISTINCT FIEL1,FIELD2 FROM TAB order by FIELD2 DESC;

BUT IT RETURN WITH
AAA 555
BBB 444
AAA 321
AAA 311
CCC 223

WHY DOESN'T IT RETURN:
AAA 555
BBB 444

CCC 223

Anon

Well, it's quite clear, why you have such output. DISTINCT affects all fields which you include in your SELECT statement. So, in your case it shows DISTINCT combination of field1 and field2. You may try:

SELECT DISTINCT(FIEL1),FIELD2 FROM TAB order by FIELD2

may be, this will exclude the second field from DISTINCT, but I'm not quite sure: I don't have big expirence in MySQL. If it does not help, DISTINCT is not what you need.

Anon

As subject tells.
What does DISTINCT do in a mysql query ?

/Staffan

Anon

As far as I understand (and how I've been using it) - it excludes duplicate rows from the result of your query. If there are several columns after DISTINCT, it will exclude similar combinations of these fields. For example, if you say:
SELECT DISTINCT Field1 FROM....
and you have in Field1
AAA
AAA
BBB
BBB,
your query will return:
AAA
BBB.
If you say:
SELECT DISTINCT Field1, Field2 FROM....
and you have in Field1, Field2:
AAA 111
AAA 222
BBB 333
BBB 333,
your query will return:
AAA 111
AAA 222
BBB 333.
Is that the answer?

Anon

has anyone tried this?

SELECT Field2, Field3 DISTINCT Field1 FROM ....

will this work?

Anon

Yes It works so you write.
But I want work in follow way:

SELECT .......... Field1, Field2 FROM....ORDER BY FIELD2 DESC

I have in Field1, Field2:
AAA 111
AAA 222
BBB 333
BBB 333,
I want query will return:
AAA 222
BBB 333.
Is It possible?

Anon

Nothing of these work right...

HELP ME!!!

Anon

The keyword DISTINCT applies to the whole "tuple".

To get your result you should use aggregate functions like COUNT,MAX,MIN,AVG,SUM in combination with GROUP BY.

SELECT FIELD1,MAX(FIELD2) AS MAXFIELD2
FROM TAB
GROUP BY FIELD2
ORDER BY FIELD2 DESC;

I never tried in mysql but this is ISO SQL and should work.

Anon

Ooops I made a typo error :

SELECT FIELD1,MAX(FIELD2) AS MAXFIELD2
FROM TAB
GROUP BY FIELD1
ORDER BY MAXFIELD2 DESC;

In the mean time, I checked with mysql and it work fine !

ciao

Anon

This work correctly but...

SELECT FIELD1,MAX(FIELD2) AS MAXFIELD2
FROM TAB
GROUP BY FIELD1
ORDER BY MAXFIELD2 DESC;

but it does this:
table
01 AAA 111
02 AAA 222
03 BBB 333
04 BBB 555,

query will return:
01 AAA 222

03 BBB 555.

I want to do this:

I have in Field1, Field2:
01 AAA 111
02 AAA 222
03 BBB 333
04 BBB 555,

I want query will return:
02 AAA 222
04 BBB 555.

Is It possible?

Thankyou.

Anon

What is this new left most column another field ? the row number ?

It seems you want to use SQL in a more procedural approach using breaks in sequences !

well, SQL is not a procedural language; SQL is much more a set language (see UNION, INTERSECT, EXISTS, ...).

PHP is a good procedural language : so load your resultset in an array and walk through your array.

Hope this helps, bye

Anon

Thank you!
I made in your way. It works like I want.
I made an array and then a query using my array.

Thanks.