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" <e...@macandbumble.com>
To: <mysql@lists.mysql.com>
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=my...@dawiz.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org