Need to pull only DISTINCT rows from this query

casbboy

Here is the query. I need to pull only rows with DISTINCT column 'type'. How can I do this?

$mathex = "(purchased-used)";
$ads = mysql_query("SELECT $mathex AS difference, ad_id, type, body, purchased, used, date FROM ads HAVING difference>0 ORDER BY difference LIMIT 4",$connection);

Thanks
Ryan

LoganK

SELECT DISTINCT $mathex AS difference, ad_id, type, body, purchased, used, date FROM ads HAVING difference>0 ORDER BY difference LIMIT 4

--
I accept no responsibility for untested code/code posted on forums.

Drakla

you should be using WHERE instead of HAVING in your query. In that example they'll still give the same result, but essentially it's still wrong.

http://www.databasejournal.com/features/mysql/article.php/3469351 look there for examples why.

casbboy

The query doesn't work at all with WHERE replacing HAVING. 'difference' is an imaginary column created in the query, which is why I have been using HAVING.

Thanks
Ryan

LoganK

He's got you there drakla 😃

ultraslacker

If LoganK's correct query doesn't do what you want, then you are attempting to break data integrity...and mysql will let you with GROUP BY. Beware of data anomalies under mysql, since you have no way of knowing which values will be returned for the other attributes.

...GROUP BY type

LoganK

All I'm concerned with is if my query does what he needs it to do... 🙂

Let me know if something else is wrong!