hmm.. that worked.. but I guess I forgot to mention.. I don't need the value
of the rev.. I wanted to return that row.. like.. let me put some more info
date rev text desc
---------------------------------------------------------
20070315 1 this is version 1 This was the first one
today
20070315 2 this is version 2 This was the first one
today, but edited
20070316 1 this is version 1 This was the first one
today
20070316 2 this is version 2 This was the first one
today but agian we needed to update.
I want to pull distinct rows (there could be as many as 30 rev's per date)
and the data on that row. so.. I'd like to get the following
20070315 2 this is version 2 This was the first one
today, but edited
20070316 2 this is version 2 This was the first one
today but agian we needed to update.
does that make sense?
----- Original Message -----
From: "Olexandr Melnyk" <omelnyk@stripped>
To: "Justin" <mysql@stripped>; <mysql@stripped>
Sent: Saturday, March 17, 2007 3:56 PM
Subject: Re: Group By / Order BY
> 1) ORDER BY is executed after GROUP BY;
> 2) In ORDER BY don't use columns that aren't in GROUP BY, unless it's an
> aggregated value;
>
> Your query can be rewritten as:
>
> select date
> , max(rev) as max_rev
> from table
> group
> by date
> order
> by max_rev desc
>
> 2007/3/17, Justin <mysql@stripped>:
>>
>> I've got an issue with group / order by.. here's what I'm wondering..
>>
>> simple table..
>> date | rev
>> ----------------------
>> 20070315 1
>> 20070315 2
>> 20070316 1
>> 20070316 2
>>
>> Query I'm running
>> SELECT * FROM `table` GROUP BY `date` order by `rev` DESC
>>
>> I would think this would return
>> 20070315 - 2
>> 20070316 - 2
>>
>> but it doesn't it returns
>>
>> 20070315 - 1
>> 20070316 - 1
>>
>> What am I missing? I'm trying to do a group by the date and return the
>> higher rev..
>>
>> thanks agian!
>>
>> Justin.
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>>
>>
> ---------------------------------
> Olexandr Melnyk,
> http://omelnyk.net/
>
