CODEWITHSUNDEEP
mysqlsqlcasemysql-error-1064
webmasters
webmasters

Reputation: 5831

What's wrong with this case SQL query?

maybe you can help, I'm using this case query, and I'm trying to do mysql weighting with it. What's wrong with it?

  SELECT *
    FROM cronjob_reloaded
   WHERE site IN ('site1.com', 'site2.com')
ORDER BY (CASE site
            WHEN 'site1.com' THEN 0.2
            WHEN 'site2.com' THEN 0.8    ) * RAND( ) DESC
   LIMIT 0 , 30

MySQL said: Documentation

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 ') * RAND() DESC LIMIT 0, 30' at line 4

Upvotes: 2

Views: 172

Answers (3)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

One possible issue is that you have not used END CASE.

Change:

CASE site
WHEN 'site1.com'
THEN 0.2
WHEN 'site2.com'
THEN 0.8
)

To:

CASE site
WHEN 'site1.com'
THEN 0.2
WHEN 'site2.com'
THEN 0.8
END CASE

Refer to: MySQL site

Upvotes: 0

Bohemian
Bohemian

Reputation: 425013

CASE must be closed with END:

CASE site
WHEN 'site1.com'
THEN 0.2
WHEN 'site2.com'
THEN 0.8
END -- Added this END to your query's CASE

Upvotes: 3

Cyril Gandon
Cyril Gandon

Reputation: 17048

Here is the right syntax of CASE

SELECT *
FROM cronjob_reloaded
WHERE site IN ('site1.com', 'site2.com' )
ORDER BY (
  CASE 
    WHEN site = 'site1.com' THEN 0.2
    WHEN site = 'site2.com' THEN 0.8 
  END
) * RAND( ) DESC
LIMIT 0 , 30

Upvotes: 3

Related Questions