From: Peter Brawley Date: March 17 2007 8:35pm Subject: Re: Group By / Order BY List-Archive: http://lists.mysql.com/mysql/205658 Message-Id: <45FC510D.1080400@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit >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" > To: "Justin" ; > 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 : >>> >>> 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=omelnyk@stripped >>> >>> >> --------------------------------- >> Olexandr Melnyk, >> http://omelnyk.net/ >> > >