Menu

Tech

English

ORDER BY criterion

Part of the Fundamentals section of Coddy's SQL journey — lesson 58 of 72.

One of the OVER () options are the ORDER BY

For example:

ROW_NUMBER() OVER (ORDER BY year DESC) as row_num

This will generate a column that will number the rows following the descending year order.

Challenge

Easy

Available tables and columns:
<strong>plants</strong>: <strong>id</strong>, <strong>hue</strong>, <strong>rough</strong>

Select all plants with a hue greater than 0.34 and add two numbering columns:

row_num_hue that will number the rows according to hue in ascending order

row_num_score that will number the rows by the following formula: rough*hue in descending order

Return only the id and the two numbering columns. Order the result by the plant id in ascending order

Cheat sheet

Use ROW_NUMBER() OVER (ORDER BY column) to generate row numbers based on a specific ordering:

ROW_NUMBER() OVER (ORDER BY year DESC) as row_num

This creates a numbered column following the descending year order. You can use ASC for ascending or DESC for descending order, and can also order by calculated expressions.

Try it yourself

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Fundamentals