SQL query DISTINCT problems

martincrumlish

Hi,

I have a DB table with 3 columns.

table(details)
columns(id, cities, address)

I need to select DISTINCT cities in column 2 but i also need to use the id column in the result.

If I use a query like this:

SELECT DISTINCT cities FROM details
then I will get all the distinct cities that I need. However, the id column wont be available in my result set as it wasn't included in the select.

Now, if I used a statement like this:

SELECT DISTINCT id, cities FROM details
I will get a lot of duplicate cities because, when combined with the id column, every combination will be distinct because each value in the id column is an auto_increment value and no 2 are the same.

Does anyone know how I can select the distinct cities in my DB but also have the id value available in my result set without it messing upo the results I want?

Thanks in advance,
Martin

Norman_Graham

Hi Martin

I'm note sure if this will work, but there is a function IGNORE INDEX (index) which ignores fields in the SELECT statement as a search parameter. I'm afraid I can't test it here, but have a look at this.

If it works as I imagine it to, then the DISTINCT parameter would be applied to the cities field. However, I could be completely off track.

HTH

Norm

Lars_Berg

Given the following data

id adress

1 A1
2 A1

Which id should be returned? 1 or 2?

However, I could be completely off track.

Yes, you are.

martincrumlish

Thanks for the replies Lars and Norman.

Norman: I had a look at that code but I don't think it works for what I am doing

Lars: Your post highlighted a problem in what I was doing. I have reworked this so what I need is now accomplished in the PHP and not in the SQL query.

Thanks,
Martin