Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: Subquery doesn't work, syntax is correct???

    I just installed Mysql 4.0.18

    I tried to run ths sql statement

    Select a.price, a.name
    from rooms
    where a.price = (Select max(price) from rooms);


    I don't believe the syntax is wrong. Can anyone help me? I checked the data in the [Rooms] table which are all valid.

    When I run:
    Select max(price) from rooms;

    It works, but when its in a subquery it doesn't.

    I get this error:
    Error 1064: You have an error in your SQL syntax. Check the manual that correspond to your MYSQL server version for the right syntax to use near 'Select max(price) from rooms' at line 3

    Any help is greatly appreciated

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    You can't use subqueries before version 4.1

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    workaround:
    PHP Code:
    select t1.price
         
    t1.name
      from rooms 
    as t1
    cross
      join rooms 
    as t2
    group
        by t1
    .price
         
    t1.name
    having t1
    .price max(t2.price
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    Or, since HAVING is not optimized in MySQL and a CROSS JOIN could be expensive on a large table, you could do:
    Code:
    SELECT price, name FROM rooms
    ORDER BY price DESC LIMIT 1;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •