Practise · Define what first and top mean

Opening your saved work…

The reading

Define what first and top mean

ORDER BY wordCount DESC puts the longest line first; ASC, the default, puts the shortest first. LIMIT 5 then keeps the first five of that order. Without ORDER BY, LIMIT 5 keeps the first five rows the engine happened to reach. In this file that is usually id order, and nothing promises it. Five rows with no ORDER BY is a sample. It is not a top five.

SELECT id, title, imdb_score
FROM movies
ORDER BY imdb_score DESC, id
LIMIT 5;

The five highest scores with a tie-break. Ids 3, 4 and 5 all score 9.0; the id decides their order. Open in the playground

Ties need a rule. Three IMDb films share a score of 9.0, and ORDER BY imdb_score DESC alone lists those three in whatever order the engine met them. ORDER BY imdb_score DESC, id breaks the tie by id, so the same five films come back in the same order every time. The tie-break is a second column after a comma; make it one that is unique, and the result is repeatable.

Exactly five and everyone tied for fifth are different requests. LIMIT 5 gives you exactly five, and a tie-break decides which of the tied rows make the cut. It never pulls the rest of the tie in. When someone asks for a top five, ask back which of the two they mean.

Describe the population before you rank it. Rick's five longest lines filters to Rick first, then sorts: the longest is id 241, at 196 words. That is one line. It does not tell you who talks most across the show; that is a question about totals per speaker, and it waits for the next chapter.

SELECT id, characterName, wordCount
FROM scripts
WHERE characterName = 'Rick'
ORDER BY wordCount DESC, id
LIMIT 5;

Rick's five longest lines, longest first. Three lines tie at 71 words; the tie-break keeps id 109 and leaves ids 443 and 778 for sixth and seventh place. Open in the playground

OFFSET skips rows of the ordered result: LIMIT 5 OFFSET 5 is lines six to ten, the second page. The playground shows at most 200 rows and labels a result that was cut off. A display that stops at 200 is not a count of 200; narrow the question, or wait for COUNT in chapter 3.

Rule of thumb. No ORDER BY, no top. Add a tie-break, then LIMIT.

Going deeper (optional, not on the exam)

LIMIT without ORDER BY is not a random sample either. The engine returns rows in the order it reads them, which depends on how the table is stored and on the plan it picks, and that changes when the table changes. Treat it as some five rows.

You can sort by an alias: ORDER BY five_more_words DESC works. ORDER BY always runs before LIMIT and OFFSET, whatever order you write them in, and DISTINCT does not sort anything.

Define what first and top mean · ORDER BY, DESC, ties, LIMIT and OFFSET · level 6 of 6

North to south

Level 6: Transfer it · Exercise 1 of 9

Same idea as Define what first and top mean, new data.

The cities table, north_american_cities, holds twelve North American cities with their country, population, latitude and longitude. Return city and latitude for the U.S. cities, north to south, ties broken by city name. Finish line: two columns, every city in the United States.

Finish line: 6 rows · columns city, latitude · in the order the question gives

Data: North American Cities.

I would like help with this exercise

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run your query to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.