,'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 col2col3
A 2 3
A 100
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
col2col3
> A 2 3
> A 100 70
> A 100080
> B20 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 col
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
