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

spib · #1 (**permalink**) 02-15-05, 06:02

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

r937 · #2 (**permalink**) 02-15-05, 09:31

you cannot use subqueries in mysql unless you are on at least version4.1

r937 · #3 (**permalink**) 02-15-05, 09:35

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

Internet Services

Web Development

Digital Marketing

Consumer Tech