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=gcdmg-mysql-2@m.gmane.org

Re: Distinct max() and separate unique value

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"
To:
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=mysql@dawiz.net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Distinct max() and separate unique value

On Tue, 20 Oct 2009, DaWiz wrote:

> 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.

You end up with the same object_id.

select max(object_id), term_taxonomy_id from wp_term_relationships where
term_taxonomy_id IN (122,127) group by term_taxonomy_id order by
term_taxonomy_id;

+----------------+------------------+
| max(object_id) | term_taxonomy_id |
+----------------+------------------+
| 1503 | 122 |
| 1503 | 127 |
+----------------+------------------+

>> 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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Distinct max() and separate unique value

----- Original Message -----
From: "Eric Anderson"
To:
Sent: Tuesday, October 20, 2009 4:05 PM
Subject: Re: 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?

I confess I did not understand what you are trying to do.

If what you actually want is the highest 'term_taxonomy_id' for each
distinct objhect_id then the query would be:

select object_id, max(term_taxonomy_id)
where term_taxonomy_id IN (122,127)group by object_id
order by object_id desc;

This query will not take into consideration term_taxonomy_Id values other
than 122 and 127, it also will not return object_id's without a
term_taxonomy_Id value of 122 or 127.





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org