Re: MySQL SQL Query Help

On Mon, 13 Nov 2006, bluefx13@gmail.com 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@purplecow.com] Sent: Lunes, 13 de Noviembre de 2006 04:59 p.m. To: PHP-DB Mailing List Subject: [PHP-DB] 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@purplecow.com                             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@purplecow.com                             http://www.purplecow.com/

---------------------------------------------------------------------------