List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:March 17 2007 8:35pm
Subject:Re: Group By / Order BY
View as plain text  
 >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

GROUP BY groups by aggregate value, not by row. Use a WHERE clause to 
return a row corresponding to the value of a column.

PB

Justin wrote:
> 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/
>>
>
>
Thread
Group By / Order BYJustin17 Mar
  • Re: Group By / Order BYOlexandr Melnyk17 Mar
Re: Group By / Order BYJustin17 Mar
  • Re: Group By / Order BYPeter Brawley17 Mar