Queries Review - experimenting with 'movies' table

Get Help SQL
#1

Hi, first time poster here, apologies if I make a mistake.

So I was playing around with the movies table from the SQL tutorial, just trying to use a few operators, clauses etc.

I popped this in, and it did not return anything.

/*This returns nothing*/
SELECT year AS 'Released',
  CASE
  WHEN year > 1990 THEN 'Modern'
  WHEN year > 1960 THEN 'Retro'
  ELSE 'Ancient!'
  ORDER BY DESC
  END AS 'Vintage'
LIMIT 20
FROM movies;

Have I made a big mistake somewhere?

Thank you in advance :slight_smile:

1 Like
#2

this is an issue. You need to change up the operator in that WHEN statement.
(Greater than 1960 is covered by the previous WHEN…). When you use a comparative operator you need to specify the col. again. ex: WHEN year > 1960 and year < 1989 THEN... or something like that.

Also, the order of the operations is incorrect. (ORDER BY AND LIMIT). You also need to specify what column to order the results by.

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
2 Likes
#3

So, I need to put ORDER BY year DESC. Gutted I missed that but thank you.

I am a bit confused about where LIMIT should be. In one of the previous tutorials, it stated LIMIT should come right at the end of the query. My placement would still be incorrect, but it would come after FROM movies…is that incorrect?

Thank you for your help

#4

Sometimes it takes a couple sets of eyes to debug stuff. Also, sometimes it helps to read the code from the bottom up, that way you might see something you missed.

LIMIT is always last.

3 Likes
#5 
/*This returns nothing*/
SELECT year AS 'Released',
  CASE
    WHEN year > 1990 THEN 'Modern'
    WHEN year > 1960 AND year < 1989 THEN 'Retro'
    ELSE 'Ancient!'
    END AS 'Vintage'
    ORDER BY year DESC
FROM movies
LIMIT 20;

This is my new code, but I still am not seeing any results. I am just entering this into my browser on the review page of the tutorial. The movies table is still there, I can SELECT * and see it all there.

Thanks

1 Like
#6

Move your ORDER BY to after FROM.

CASE statements only have conditional logic in them-- if, elif, else (WHEN.....THEN....ELSE) It tests the conditions and then applies something to it, or assigns a value to it.

Also, if you limit it to 20 records you’ll only get “Modern”.

3 Likes
#7 
/*This returned results, hooray*/
SELECT DISTINCT year AS 'Released',
  CASE
  WHEN year >= 1990 THEN 'Modern'
  WHEN year BETWEEN 1960 AND 1989 THEN 'Retro'
  ELSE 'Ancient!'
  END AS 'Vintage'
  FROM movies
  ORDER BY year DESC
LIMIT 50;

Using this, I was finally able to return some results. Thank heavens!

Interestingly, if I swap around 1960 and 1989, then those rows will be returned as ‘Ancient!’ and not ‘Retro’

One for me to ponder I think :slight_smile:

Thanks

1 Like
#8

Just remember that when you use DISTINCT you’ll only get one entry per year b/c it filters the rows for a unique value.

Also, when doing comparisons sometimes it’s good to write out (or draw) what you want your results to be or, what you think they will be.

1 Like