Results 1 to 7 of 7

Thread: inline view ?

  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: inline view ?

    This is the query that works on MS SQL server, but I can't get to work in MySQL:

    SELECT ss, COUNT(DISTINCT ra) AS theCount
    FROM (SELECT DISTINCT searchStr AS ss, remote_addr AS ra FROM tbl_savesearch) tb
    GROUP BY ss
    ORDER BY theCount DESC

    table looks like this:

    table tbl_savesearch
    id, searchStr, remote_addr, was_answered

    Tried to find an answer in the documentation, but the only thing I could find that is wrong is that mysql needs an "as" before the "tb" after the second inline query.

    What do I need to change?

  2. #2
    Join Date
    Nov 2003
    Posts
    91
    What was the error message?
    Prior to version 4.1 MySQL does not support subselects.
    select version();

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    ah, yes that seems to be the problem:

    "[MySQL][ODBC 3.51 Driver][mysqld-3.23.58]You have an error in your SQL syntax near..."

    Is there some other way to get the same result?

  4. #4
    Join Date
    Nov 2003
    Posts
    91
    This should do the same thing:

    DROP TABLE IF EXISTS tmp;
    CREATE TEMPORARY TABLE tmp
    SELECT DISTINCT searchStr as ss, remote_addr as ra
    FROM tbl_savesearch;

    SELECT ss, COUNT(DISTINCT ra) AS theCount
    FROM tmp
    GROUP BY ss
    ORDER BY theCount DESC;

    DROP TABLE tmp;

  5. #5
    Join Date
    Jan 2004
    Posts
    4
    Actually I couldn't get that to work either, this is the error message:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [MySQL][ODBC 3.51 Driver][mysqld-3.23.58]You have an error in your SQL syntax near ';CREATE TEMPORARY TABLE tmp SELECT DISTINCT searchStr as ss, remote_addr as ra F' at line 1

    I tried adding a semicolon after "create temporary table tmp" too, but that made no difference...

  6. #6
    Join Date
    Nov 2003
    Posts
    91
    Try running all those SQL queries w/o the terminating semicolon (.

  7. #7
    Join Date
    Nov 2003
    Posts
    91
    And run each of the four SQL queries in a separate ODBC statement (don't jam them all together in one ODBC statement).

Posting Permissions

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