[SOLVED] DISTINCT

[mcmuney]

I'm using the following SQL, which shows duplicate zipcodes. How can I use DISTINCT so that every zipcode shows are unique?

 

$sql="SELECT `RESULTS`.`ZIPCODE`, `DATE`, 	`RESULTS`, `CITY`, `STATE_NAME` FROM `RESULTS` INNER JOIN `ZIPCODES` on (`ZIPCODES`.`ZIPCODE`=`RESULTS`.`ZIPCODE`) ORDER BY DATE DESC  LIMIT 25 ";

[Maq]

Try:

 

$sql="SELECT R.ZIPCODE, R.DATE, R.RESULTS, Z.CITY, Z.STATE_NAME FROM RESULTS R LEFT JOIN ZIPCODES Z on (Z.ZIPCODE=R.ZIPCODE) ORDER BY DATE DESC LIMIT 25";

 

I cleaned and shortened up your code a bit.  Your style of SQL queries is odd, hard to follow.

 

Anyway, I think you needed a left join and you weren't declaring what tables you got what column from.

 

PS - This is a MySQL related question, not PHP.

[mcmuney]

Thanks for the cleanup on the SQL. I posted here after reading your reply. I also requested for the other post to be deleted.

 

To add a bit more info. There are multiple rows of the zipcode where additional information are different. But I want to only show the latest (date) zipcode. For example, DB may have:

 

90210 4/3/09 123 (only want to show this row, the latest)

90210 4/2/09 456

90210 4/1/09 987

[fenway]

Then you'll need to gropu by zipcode and grab the most recent, and THEN join that result to the remaining info.