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