Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Unanswered: using subquery in mysql (was "Help get this query working")

    Hi,

    I have a table called 'myTable' which consists of two fields, field_id (int) and time (DateTime). I have a query which works fine in SqlServer which won't work in MySql.

    Code:
    select *, (select count(*) + 1 from myTable t where t.time < m.time) as Rank from myTable m order by Rank desc
    The query is supposed to display a list of times with a ranking. When I run the above query I get an error

    Code:
    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count(*) + 1 from myTable t whe
    Can anyone help me?

    Cheers

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you cannot use subqueries in mysql unless you are on at least version4.1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to use the following solution, you cannot use "select star"

    you must itemize all the columns in both the SELECT list and the GROUP BY
    Code:
    select t.foo
         , t.bar
         , t.time
         , count(*) + 1 as Rank
      from myTable as t
    left outer
      join myTable as m  
        on t.time < m.time
    group
        by t.foo
         , t.bar
         , t.time
    order 
        by Rank desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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