distinct values

[yebaws]

I have a table with several columns that have repeated values. I want to select and list only the unique values from 2 columns and list them alternately.

This code works as expected to select unique vales from column 'listing_location' where area column is 'area1' and list them:

$query = " SELECT DISTINCT listing_location FROM listings WHERE area = 'area1' LIMIT 10";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
	
echo (", {$row['business_category']");},

And the same code works for column 'business_category'

But when I try and combine the 2, like this:

$query = " SELECT DISTINCT business_category, listing_location FROM listings WHERE area = 'area1'  LIMIT 10";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
	
echo (", {$row['business_category']}, {$row['listing_location']}");}

The values get repeated. Seems that distinct values are now only recognised as one where the *combination* of business_category and listing_location are unique.

Is there an easy way to do what I want with a single query, or do I need to do 2 separate queries and build a string to display the values alternately? And if the latter, how do I do this?

Thanks.

 

[jpadie]

i don't really understand what you are asking.

let's assume you have a table with three fields

1. business_category
2. listing_location
3. area

and let's assume some data

auto, classified, london
auto, classified, london
auto, premium, london
housing, classified, london
housing premium, london

what results do you expect?

I suspect you want to be using groupby rather than distinct but will suspend judgment until i see what data you are after.

You also might get better advice from the mysql forum as your query appears to be wholly related to sql structure and not php.

 

[ingresman]

But do you mean rows not columns in your original description and the distinct with a lsit of columns is distinct accross all. Just out of interest did you expect distinct to either use the first column or a key value ?
I think you might get away with using a subselect where you select distinct on businses category and use that to drive your main query.
Yes over to the mysql forum where more help should be forthcoming.

 

[DonQuichote]

You'd better ask SQL questions in a database forum. But I think a GROUP BY clause can do exactly what you want. Off course, you have to specify what you want to do with the third column.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)