CODEWITHSUNDEEP
mysqlunion
xiomai
xiomai

Reputation: 89

MySQL Union error

Why do i get this error with this query?

SELECT distinct fileID 
FROM ( SELECT fileID from file order by fileID desc limit 30) as R1 
UNION (SELECT fileID from visit order by counter desc limit 30 ) 
WHERE status = 1 order by rand() LIMIT 10

error:

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 'WHERE status = 1 order by rand() LIMIT 10' at line 1

what i want is to select the top 30 most viewed file and top 30 most recent file and then randomly select from them limit by 10 with the file status = 1.

Upvotes: 1

Views: 3094

Answers (3)

Kaii
Kaii

Reputation: 20540

put your two queries for newest files and most visits in a UNION inside a subquery, and then reorder the resulting derived table by RAND and limit the result to 10.

Note:

you state you want to select recently visited files.. but ordering by "counter" in fact selects the files with the most visits .. not the recent ones.

SELECT DISTINCT fileID 
FROM (
  SELECT file.fileID 
    FROM file 
    WHERE file.status = 1 
    ORDER BY file.fileID DESC 
    LIMIT 30
  UNION ALL
  SELECT visit.fileID 
    FROM visit 
      JOIN file ON file.fileID = visit.fileID 
    WHERE file.status = 1 
    ORDER BY visit.counter DESC 
    LIMIT 30 
) dt
ORDER BY RAND() 
LIMIT 10

As a reaction to the OPs comment, i added a JOIN in one of the selects to check if the file related to a visit has status = 1.

Also Note:

for this query to perform fast on larger amounts of data you should add index on the fields file.status and visit.counter. If fileID is not already a key / index for those tables, you should add an index on this field too.

The visit.file_id is not UNIQUE so the above query may have the same file_ids in the sub-result of the 2nd subquery (the duplicates will be removed by the final DISTINCT but that means that the second LIMIT 30 does not work as wanted). Possible correction:

SELECT DISTINCT fileID 
FROM (
  SELECT file.fileID 
    FROM file 
    WHERE file.status = 1 
    ORDER BY file.fileID DESC 
    LIMIT 30
  UNION ALL
  SELECT visit.fileID 
    FROM visit 
      JOIN file ON file.fileID = visit.fileID 
    WHERE file.status = 1 
    GROUP BY visit.fileId
    ORDER BY MAX(visit.counter) DESC 
    LIMIT 30 
) dt
ORDER BY RAND() 
LIMIT 10

Upvotes: 2

xdazz
xdazz

Reputation: 160833

Try this, you need to parentheses to close the select.

SELECT DISTINCT fileid
FROM   ((SELECT fileid, status 
         FROM   FILE
         ORDER  BY fileid DESC
         LIMIT  10)
        UNION
        (SELECT fileid, status 
         FROM   visit
         ORDER  BY counter DESC
         LIMIT  10)) AS T
WHERE  status = 1
ORDER  BY Rand()
LIMIT  10

Upvotes: 0

yen1k
yen1k

Reputation: 556

What you want to do with this query? I think, there is more parsing errors.

I don't know if MySQL takes this.. ..and if is that You want to do...

    SELECT distinct fileID FROM (( SELECT fileID from file order by
fileID desc limit 10) as R1 UNION (SELECT fileID from visit order by
counter desc limit 10 ) as R2) WHERE R1.status = 1 order by R1.rand() LIMIT 10

Upvotes: 0

Related Questions