On Mon, 13 Nov 2006, bluefx13@xxxxxxxxx wrote:
Actually, that should not work, it should give you an error.
This should work:
SELECT `fkid`,max(`foo`) as foo,`bar` FROM `test2` GROUP BY `fkid` ORDER BY `bar` ASC
Yes, but if the data is in a different order that fails and doesn't maintain row order:
mysql> create temporary table test2 (id tinyint,fkid tinyint, foo smallint, bar varchar(20));
mysql> insert into test2 values (1,1,34,'red'), (2,1,345,'yellow'), (3,2,345,'brown'), (4,2,3459,'green');
mysql> select * from test2;
+------+------+------+--------+
| id | fkid | foo | bar |
+------+------+------+--------+
| 1 | 1 | 34 | red |
| 2 | 1 | 345 | yellow |
| 3 | 2 | 345 | brown |
| 4 | 2 | 3459 | green |
+------+------+------+--------+
mysql> SELECT `fkid`,max(`foo`) as foo,`bar` FROM `test2` GROUP BY `fkid` ORDER BY `bar` ASC;
+------+------+-------+
| fkid | foo | bar |
+------+------+-------+
| 2 | 3459 | brown |
| 1 | 345 | red |
+------+------+-------+
2 rows in set (0.00 sec)
Notice how 3459 is supposed to be green but reports brown, and 345 should
be yellow but reports red?
Any other solutions that maintain row integrity?
Beckman
Miguel Guirao wrote:
select max(bar) from mytable where unique fkid order by bar asc
as far as I remember!!
-----Original Message-----
From: Peter Beckman [mailto:beckman@xxxxxxxxxxxxx]
Sent: Lunes, 13 de Noviembre de 2006 04:59 p.m.
To: PHP-DB Mailing List
Subject: MySQL SQL Query Help
I have a table:
id fkid foo bar
1 1 345 yellow
2 1 34 red
3 2 3459 green
4 2 345 brown
I want to select the largest value of "foo" for a unique fkid, and return
bar, the results ordered by bar. In this case, 345 is the largest value of
foo for fkid 1, and 3459 is the largest for fkid 2. "green" comes before
"yellow." My desired result set would be:
fkid foo bar
2 3459 green
1 345 yellow
How would I write that in SQL? fkid and foo are ints, bar is a varchar.
Beckman
---------------------------------------------------------------------------
Peter Beckman Internet Guy
beckman@xxxxxxxxxxxxx http://www.purplecow.com/
---------------------------------------------------------------------------
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
---------------------------------------------------------------------------
Peter Beckman Internet Guy
beckman@xxxxxxxxxxxxx http://www.purplecow.com/
---------------------------------------------------------------------------
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
