I would try:
select max(object_id), term_taxonomy_id
group by term_taxonomy_id
order by term_taxonomy_id;
max(column) returns a single value so distinct is not needed.
The group by and order by should only have columns thaqt are displayed and
that are not aggregate columns.
----- Original Message -----
From: "Eric Anderson" <eric@stripped>
To: <mysql@stripped>
Sent: Tuesday, October 20, 2009 3:42 PM
Subject: Distinct max() and separate unique value
>
> I'm trying to formulate a query on a Wordpress database that will give me
> the highest 'object_id' with the highest 'term_taxonomy_id', something
> like:
>
> +-------------------------+------------------+
> | max(distinct object_id) | term_taxonomy_id |
> +-------------------------+------------------+
> | 1503 | 127 |
> | 1494 | 122 |
> +-------------------------+------------------+
>
> But I just can't seem to get there?
>
> select max(distinct object_id), term_taxonomy_id from
> wp_term_relationships where term_taxonomy_id IN (122,127) group by
> term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc
>
> +-------------------------+------------------+
> | max(distinct object_id) | term_taxonomy_id |
> +-------------------------+------------------+
> | 1503 | 127 |
> | 1481 | 127 |
> | 300 | 127 |
> | 1503 | 122 |
> | 1494 | 122 |
> | 1470 | 122 |
> | 1468 | 122 |
> | 1205 | 122 |
> | 1062 | 122 |
> | 316 | 122 |
> | 306 | 122 |
> | 228 | 122 |
> | 222 | 122 |
> | 216 | 122 |
> | 211 | 122 |
> | 184 | 122 |
> | 155 | 122 |
> | 149 | 122 |
> | 134 | 122 |
> | 128 | 122 |
> | 124 | 122 |
> | 119 | 122 |
> | 113 | 122 |
> | 109 | 122 |
> | 105 | 122 |
> | 93 | 122 |
> | 91 | 122 |
> | 87 | 122 |
> +-------------------------+------------------+
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
