You're looking for the rows containing the group-wise maximum. There's an explanation in the manual, section 3.6.4, http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html
On 8/5/05, Kemin Zhou <[EMAIL PROTECTED]> wrote: > I have a simple table > > col1 col2 col3 > A 2 3 > A 100 70 > A 1000 80 > B 20 90 > B 70 80 > > > To select the top one row for each unique value of col1 > > select distinct on (col1), col1, col2, col3 > from table > order by col1, col2 desc, col3 desc; > > What I want is > A 1000 80 > B 70 80 > > How do you do it in mysql? > > Kemin > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
