Mysql Select distinct problem

[Anon]

Hi,

Does anyone knows how to filter a table for unique fields and display the corresponding reference number for each filtered field.

Let me explain :

select DISTINCT Categorie from tbl_Catalogue

returns the correct data but I also need the corresponding reference field number like this :

select DISTINCT Categorie, Ref from tbl_Catalogue

However this query doesn't filter anything.

I'm using MySQL 3.23

Thanks

Jean-François

[[deleted]]

What you are asking is not possible.

There are several 'Ref' values for each 'Categorie', so if you want to select only one of every possible 'Categorie' value
you MUST specify which of the corresponding 'Ref' values you want.

[Anon]

So if it's not possible what is the workaround because I still need to filter a table and display a corresponding field. In the first message I used ReferenceID as an example but what I really need is to display a field and for this purpose it does not matter which one because it corresponds to an double name field much like First Name and Last Name to illustrate.

Sincerely,

Jean-François

[[deleted]]

If it doesn't matter which of the corresponding values you get, you can make use of the fact that MySQL is crap:

SELECT this, that
FROM table
GROUP BY this

Now you will get all the possible values of 'this' once, and 'that' will be a random choice from the available 'that' values for each unique value of 'this'.

Try it and you'll see what I mean.

[Anon]

I need to use DISTINCT to filter unique product then display the supplier.

eg :

Field

Operating System Supplier

Windows 98 Microsoft
Windows 2000 Microsoft
MySQL MySQL AB

...

If the following query doesn't work then what would give me the above results ?

SELECT DISTINCT Operating System, Supplier
FROM tbl_Catalogue
ORDER BY Operating SYSTEM

Thank you Vincent for your help.

Regards

Jean-François

[[deleted]]

Forget DISTINCT, use GROUP BY

SELECT OS, Supplier
FROM tbl_Catalogue
GROUP BY OS

But remember, this only works because MySQL does not use the correct GROUP BY functionality.

[Anon]

Thank you Vincent you are a genius. Do you know how long I've been looking for this one, well, actually you don't want to know.

I'm forever thankful Vincent.

Cheers,

Jean-François

[[deleted]]

I'll send the bill later :-)