Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable.

The newly created view works properly and all SQL is correct.

This is the error message I get when I try to view all records from the view (or when I click on "Browse" in phpMyAdmin):

#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 'THEN0 ELSE1 END LIMIT 0, 30' at line 1

Here is example schema:

CREATE TABLE `comments` (
  `user_id` int(11) NOT NULL,
  `title` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
INSERT INTO `comments` (`id`, `user_id`, `title`) VALUES
(1, 1, 'user one comment 1'),
(2, 1, 'user one comment 2'),
(3, 2, 'user two comment 1'),
(4, 2, 'user two comment 2');

CREATE TABLE `users` (
  `name` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
INSERT INTO `users` (`id`, `name`) VALUES
(1, 'user1'),
(2, 'user2');

Sample view:

CREATE VIEW view_sample AS
SELECT AS comment_id,
  c.title AS comment_title, AS user_name
FROM comments c, users u
WHERE c.user_id =

At this point you can verify that the view above works properly and shows results.

Sample query that will break everything:

SELECT * FROM view_sample
WHERE comment_title LIKE 'user one%'
ORDER BY CASE WHEN comment_title LIKE 'user one%' THEN 1 ELSE 0 END, comment_title

Query above will work, but whatever you try to do afterwards will fail. Try simply this:

SELECT * FROM view_sample

I tried this several times, with different views.

What am I doing wrong?

share|improve this question
This is the kind of thing you should test in the MySQL command line client, because it sounds like phpmyadmin, which is 3rd party software, might be breaking your query, if I understand the issue as you describe it, as opposed to a problem in MySQL. – Michael - sqlbot Nov 12 '13 at 12:41

1)Syntax error: need add space between THEN and 0 (eq. ELSE).

share|improve this answer
Thanks for the answer, but it's not the case. View is created properly and the syntax for creating is correct. I am preparing now sample code so I can show my point. – Aram Boyajyan Nov 12 '13 at 11:38
Sample code is added to the original post. – Aram Boyajyan Nov 12 '13 at 11:50
this is code good working , this result log(mysql server 5.6): 0:23:28 2 Query select * from view_sample – Vfvtnjd Nov 12 '13 at 12:23
Is it possible that this is related to my setup? I'm testing this on MAMP and phpMyAdmin that is shipped with it. Version of MySQL is 5.5.29. – Aram Boyajyan Nov 12 '13 at 12:34

I tried your schema / query on SqlFiddle with:

MySql 5.5.32 (show here)

MySql 5.6.6 m9 (show here)

and work fine together.

Please check your PhpMyAdmin possibly fault

share|improve this answer

Are you talking about the error as shown in this SQL Fiddle?

When you add semi-colons, do you get results from the first query and none from the second like in this SQL Fiddle?

If you are expecting results like in this SQL Fiddle, you will likely need to use a different client. The difference is the batch terminator.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.