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(M
mysql> SELECT VERSION();
+---+
| VERSION() |
+---+
| 4.1.7-nt |
+---+
1 row in set (0.00 sec)
mysql> CREATE TABLE test (col1 INT, col2 INT, col3 INT);
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO test VALUES (1, 1, 1), (1, 2, 3);
Query OK, 2 rows affected (0.02
[snip]
> SELECT col1, MAX(col2), col3 FROM table GROUP BY col1;
Because col3 is not part of the GROUP BY clause, this query will not
guarantee that the col3 returned is associated with the col2 returned.
In other words, running this query on the table containing
col1 / col2 / col3
1 1
> SELECT col1, MAX(col2), col3 FROM table GROUP BY col1;
Because col3 is not part of the GROUP BY clause, this query will not
guarantee that the col3 returned is associated with the col2 returned.
In other words, running this query on the table containing
col1 / col2 / col3
1 11
1
[snip]
The same way you do it inother SQL's.
SELECT MAX(col2) FROM table GROUP BY col1;
[/snip]
Oops;
SELECT col1, MAX(col2), col3 FROM table GROUP BY col1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[snip]
I have a simple table
col1 col2col3
A 2 3
A 100 70
A 100080
B20 90
B7080
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;
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 col2col3
> A 2
