Rules for SQL clause order

Get Help SQL
#1

Hi,

Wondering where I can find a reference document for clause order rules? While working through the Hacker News project, I found this on the cheatsheet for the Aggregate Functions lesson:

" The GROUP BY clause can come after FROM or WHERE but must come before any ORDER BY or LIMIT clause.

The given query will count the number of movies per rating."

Tried to run the following:
“SELECT strftime(‘%H’, timestamp) WHERE timestamp IS NOT NULL AS ‘Publication Hour’,
ROUND(AVG(score), 1) AS ‘Average Score’,
COUNT(*) AS ‘Number of Stories’
FROM hacker_news
GROUP BY 1
ORDER BY 1;”

But learned (from the Hint Section), that the WHERE timestamp IS NOT NULL needed to be positioned as follows:

“SELECT strftime(‘%H’, timestamp) AS ‘Publication Hour’,
ROUND(AVG(score), 1) AS ‘Average Score’,
COUNT(*) AS ‘Number of Stories’
FROM hacker_news
WHERE timestamp IS NOT NULL
GROUP BY 1
ORDER BY 1;”

Learn SQL: Queries Cheatsheet | Codecademy
Learn SQL: Aggregate Functions Cheatsheet | Codecademy
Aggregate Functions | Codecademy

#2

Because the WHERE clause doesn’t go in the SELECT statement, it follows FROM.

SELECT
FROM
JOIN
ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

If you just google ‘order of operation’ you’re also going to find ‘order of execution’, which is how the queries run “behind the scenes”.

1 Like
#3

Thank you so much Lisa!

1 Like