Re: top one row
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 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; 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]
RE: top one row
[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; What I want is A 1000 80 B 70 80 How do you do it in mysql? [/snip] The same way you do it inother SQL's. SELECT MAX(col2) 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]
RE: top one row
[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]
Re: top one row
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 23 may return 1 21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: top one row
[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 11 1 23 may return 1 21 [/snip] I have tested this several times and never got those kind of results, do you have some docs? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: top one row
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 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT * FROM test; +--+--+--+ | col1 | col2 | col3 | +--+--+--+ |1 |1 |1 | |1 |2 |3 | +--+--+--+ 2 rows in set (0.00 sec) mysql SELECT col1, MAX(col2), col3 FROM test GROUP BY col1; +--+---+--+ | col1 | MAX(col2) | col3 | +--+---+--+ |1 | 2 |1 | +--+---+--+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: top one row
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 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; 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]
