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" <[EMAIL PROTECTED]>
To: "Justin" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
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 <[EMAIL PROTECTED]>:
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/[EMAIL PROTECTED]
---------------------------------
Olexandr Melnyk,
http://omelnyk.net/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
