This is out of the MySQL class and is called the Max-Concat trick.
________________________________________________________________________
____
What is the biggest country in each continent?
"The Rows Holding the Group-wise Maximum of a Certain Field"
MySQL> Select Continent,
-> SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS
Country,
-> 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS
Population
-> From Country
-> Group By Continent
-> Order By Population DESC;
+---------------+----------------------------------------------+--------
----+
| Continent | Country |
Population |
+---------------+----------------------------------------------+--------
----+
| Asia | China |
1277558000 |
| North America | United States |
278357000 |
| South America | Brazil |
170115000 |
| Europe | Russian Federation |
146934000 |
| Africa | Nigeria |
111506000 |
| Oceania | Australia |
18886000 |
| Antarctica | South Georgia and the South Sandwich Islands |
0 |
+---------------+----------------------------------------------+--------
----____________________________________________________________________
________
It looks ugly but what you have to do is tie the data you want together
and let the max work on the collection and then split it back out again
in the display.
So in your case
SELECT col1,
LEFT(MAX(CONCAT(LPAD(col2,10,'0'),col3)),10) + 0 AS col2,
MID((MAX(CONCAT(LPAD(col2,10,'0'),col3)),11,50) + 0 As col3
FROM sample
GROUP BY col1
-----Original Message-----
From: Kemin Zhou [mailto:[EMAIL PROTECTED]
Sent: Friday, August 05, 2005 12:38 PM
To: mysql@lists.mysql.com
Subject: top one row
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]
