Simplified
Simplified

L6.1Putting rows in order

YOU

Putting rows in order

SELECT nameFROM usersWHERE name ILIKE'eli%';resultnamenameEliEliEli
SELECT name, xpFROM users;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75result, next timenamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75?
SELECT name, xpFROM usersORDER BYxpDESCASC;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75?
YouTube: PopularSELECT title, viewsFROM videosORDER BY views DESC;videostitletitleviewsviewsPizza dough3,000,000Pizza dough3,000,000Pizza dough3,000,000Taco night850,000Taco night850,000Taco night850,000Soup basics12,000Soup basics12,000Soup basics12,000
Amazon: Price, low to highSELECT title, priceFROM casesORDER BY price;casestitletitlepricepriceBlack case$9Black case$9Black case$9Clear case$12Clear case$12Clear case$12Wallet case$20Wallet case$20Wallet case$20
Spotify: Top 50SELECT title, playsFROM songsORDER BY plays DESC;songstitletitleplaysplaysEspresso9,100,000Espresso9,100,000Espresso9,100,000Not Like Us8,400,000Not Like Us8,400,000Not Like Us8,400,000Saturn5,200,000Saturn5,200,000Saturn5,200,000

Databases · Lesson 6.1

Sorting

Putting rows in order

Simplified
0:00 / 2:09

So ILIKE kept Eli's row, because it matched his capital E to the small e.

WHERE picked which rows came back, but not their order.

Now say Simplified's analytics screen wants every learner's name and XP.

So it asks Postgres: SELECT name, XP FROM users.

They came back as Dee, Ben, Fay, Ada, Cy and Eli.

SELECT name, xpFROM users;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75

But will they come back in that order every time?

No: the query never asked for an order, so Postgres promises none.

It sends the rows in whatever order it finds them.

So send the query again, and the order can change.

This time: Cy, Dee, Eli, Ada, Ben and Fay.

Both orders are correct, because any order answers the query.

SELECT name, xpFROM users;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75result, next timenamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75

So how does the team get the learners from most XP to least, every time?

It adds ORDER BY to the end of the query, with a column.

ORDER BY sorts the result by that column.

DESC after the column means descending: highest first.

So the query says SELECT name, XP FROM users ORDER BY XP DESC.

SELECT name, xpFROM usersORDER BYxpDESC;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75

Dee's 300 comes first, then Ada's 120, then Fay's 75.

Then Ben's and Eli's 40s, and Cy's 0 comes last.

And what if the team wants the fewest XP first?

ASC after the column means ascending: lowest first.

And it's the default: with neither word, ORDER BY sorts lowest first.

So ORDER BY XP alone puts Cy's 0 first, and Dee's 300 last.

SELECT name, xpFROM usersORDER BYxp;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75
YouTube: PopularSELECT title, viewsFROM videosORDER BY views DESC;videostitletitleviewsviewsPizza dough3,000,000Pizza dough3,000,000Pizza dough3,000,000Taco night850,000Taco night850,000Taco night850,000Soup basics12,000Soup basics12,000Soup basics12,000

Tap Popular on a YouTube channel, and it might send ORDER BY views DESC.

So the most viewed video, Pizza dough, comes first.

Amazon: Price, low to highSELECT title, priceFROM casesORDER BY price;casestitletitlepricepriceBlack case$9Black case$9Black case$9Clear case$12Clear case$12Clear case$12Wallet case$20Wallet case$20Wallet case$20

On Amazon, sort phone cases by price, low to high.

It might send ORDER BY price.

With no DESC, it sorts lowest first.

So the cheapest case, the Black case, comes first.

Spotify: Top 50SELECT title, playsFROM songsORDER BY plays DESC;songstitletitleplaysplaysEspresso9,100,000Espresso9,100,000Espresso9,100,000Not Like Us8,400,000Not Like Us8,400,000Not Like Us8,400,000Saturn5,200,000Saturn5,200,000Saturn5,200,000

Open Spotify's Top 50, and it might send ORDER BY plays DESC.

So the song with the most plays, Espresso, comes first.

Example: Putting rows in order

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users ORDER BY xp DESC;.

Who comes first in the result?

Explanation

First, ORDER BY xp sorts the rows by XP, and DESC puts the highest first.

Then, the highest XP is Dee's 300.

Therefore, Dee comes first.

Question 1

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users ORDER BY xp ASC;.

Who comes last in the result?

Who comes first in a tie?

SELECT name, xpFROM usersORDER BYxpDESC,name;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75tie?
YouTube: PopularSELECT title, viewsFROM videosORDER BY views DESC,title;videostitletitleviewsviewsPizza dough3,000,000Pizza dough3,000,000Pizza dough3,000,000Bread at home1,000,000Bread at home1,000,000Bread at home1,000,000Pasta night1,000,000Pasta night1,000,000Pasta night1,000,000Soup basics12,000Soup basics12,000Soup basics12,000tie
Amazon: Price, low to highSELECT title, priceFROM casesORDER BY price,title;casestitletitlepricepriceBlack case$9Black case$9Black case$9Clear case$12Clear case$12Clear case$12Leather case$12Leather case$12Leather case$12Wallet case$20Wallet case$20Wallet case$20tie

Databases · Lesson 6.2

Sorting

Who comes first in a tie?

Simplified
0:00 / 1:34

So ORDER BY XP DESC sent back Dee's 300 first, and Cy's 0 last.

But in between, Ben and Eli both have 40 XP.

So which of them comes first?

Two rows with the same value in the sorted column are a tie.

A tie can come back in either order: Ben then Eli, or Eli then Ben.

Both are correct, because ORDER BY XP DESC only sorts by XP.

SELECT name, xpFROM usersORDER BYxpDESC;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75result, next timenamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75

So how does the team make Ben come first, every time?

It adds a second column after a comma: ORDER BY XP DESC, name.

Postgres sorts by XP first, and uses name only to break a tie.

DESC belongs to XP alone, so name sorts lowest first.

And for text, lowest first means alphabetical: A first.

SELECT name, xpFROM usersORDER BYxpDESC,name;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75tie

Dee, Ada and Fay each have a different XP, so name never moves them.

Ben and Eli tie on 40, so Postgres compares their names.

B comes before E in the alphabet, so Ben goes first, every time.

YouTube: PopularSELECT title, viewsFROM videosORDER BY views DESC,title;videostitletitleviewsviewsPizza dough3,000,000Pizza dough3,000,000Pizza dough3,000,000Bread at home1,000,000Bread at home1,000,000Bread at home1,000,000Pasta night1,000,000Pasta night1,000,000Pasta night1,000,000Soup basics12,000Soup basics12,000Soup basics12,000tie

Say two YouTube videos on a channel both have 1,000,000 views.

So they tie.

Then the channel sends ORDER BY views DESC, title.

B comes before P, so Bread at home goes first, every time.

Amazon: Price, low to highSELECT title, priceFROM casesORDER BY price,title;casestitletitlepricepriceBlack case$9Black case$9Black case$9Clear case$12Clear case$12Clear case$12Leather case$12Leather case$12Leather case$12Wallet case$20Wallet case$20Wallet case$20tie

Say two phone cases on Amazon both cost 12 dollars: another tie.

Then Amazon sends ORDER BY price, title.

C comes before L, so the Clear case goes first, every time.

Example: Who comes first in a tie?

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users ORDER BY xp DESC, name;.

Who comes fourth in the result?

Explanation

First, xp DESC puts Dee, Ada and Fay first, with 300, 120 and 75.

Then, Ben and Eli tie on 40, and name puts Ben before Eli.

Therefore, Ben comes fourth.

Question 1

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users ORDER BY xp;.

Who comes second in the result?

Question 2

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users ORDER BY xp, name;.

Who comes third in the result?

Question 3

Say Spotify keeps a playlist in a Postgres table, songs, with a plays column. Song A has 500 plays, Song B 500 and Song C 900. Its server sends Postgres SELECT title FROM songs ORDER BY plays DESC;.

Which song comes second?

Question 4

Say Spotify keeps a playlist in a Postgres table, songs, with a plays column. Song A has 500 plays, Song B 500 and Song C 900. Its server sends Postgres SELECT title FROM songs ORDER BY plays DESC, title;.

In what order do the songs come back?

Question 5

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users ORDER BY xp DESC, name;.

Whose order does name decide?

Question 6

Say Amazon keeps phone cases in a Postgres table, cases, with a price column. The Wallet case costs 20 dollars, the Clear case 9, and the Black case 9. Its server sends Postgres SELECT title FROM cases ORDER BY price, title;.

Which case comes first?

Just the first few rows

SELECT name, xpFROM usersORDER BYxpDESC,nameLIMIT3;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75⋮ 2,000,000 rowsAnalytics: most XP1.Dee300 XP2.Ada120 XP3.Fay75 XP?
Spotify: Top 50SELECT titleFROM songsORDER BY plays DESCLIMIT 50;songstitletitlemost playedmost playedmost played2nd most played2nd most played2nd most played3rd most played3rd most played3rd most played⋮⋮⋮50th most played50th most played50th most played51st most played51st most played51st most played52nd most played52nd most played52nd most played
Amazon: Price, low to highSELECT titleFROM casesORDER BY priceLIMIT 48;casestitletitlecheapestcheapestcheapest2nd cheapest2nd cheapest2nd cheapest3rd cheapest3rd cheapest3rd cheapest⋮⋮⋮48th cheapest48th cheapest48th cheapest49th cheapest49th cheapest49th cheapest50th cheapest50th cheapest50th cheapest
SELECT name, xpFROM usersLIMIT 3;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75result, next timenamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75?

Databases · Lesson 6.3

Sorting

Just the first few rows

Simplified
0:00 / 1:23

So ORDER BY XP DESC, name sent back Ben before Eli.

And it sent back every learner, all 2,000,000 of them.

Now say the analytics screen only has room for three learners.

It needs three rows, but Postgres would send it 2,000,000.

SELECT name, xpFROM usersORDER BYxpDESC,name;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75⋮ 2,000,000 rowsAnalytics: most XP1.Dee300 XP2.Ada120 XP3.Fay75 XP

So how does the screen ask for just the first three rows?

It adds LIMIT at the very end, with a number.

LIMIT caps how many rows come back: at most that number.

So the query ends with LIMIT 3.

SELECT name, xpFROM usersORDER BYxpDESC,nameLIMIT3;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75⋮ 2,000,000 rowsAnalytics: most XP1.Dee300 XP2.Ada120 XP3.Fay75 XP

Postgres sorts the rows first: most XP first, then by name.

Then it keeps the first three: Dee, Ada and Fay.

And it sends back only those three rows.

So the server never gets 2,000,000 rows just to show three.

Spotify: Top 50SELECT titleFROM songsORDER BY plays DESCLIMIT 50;songstitletitlemost playedmost playedmost played2nd most played2nd most played2nd most played3rd most played3rd most played3rd most played⋮⋮⋮50th most played50th most played50th most played51st most played51st most played51st most played52nd most played52nd most played52nd most played

Open Spotify's Top 50, and it might send ORDER BY plays DESC LIMIT 50.

So the 50 most played songs come back, the most played on top.

Amazon: Price, low to highSELECT titleFROM casesORDER BY priceLIMIT 48;casestitletitlecheapestcheapestcheapest2nd cheapest2nd cheapest2nd cheapest3rd cheapest3rd cheapest3rd cheapest⋮⋮⋮48th cheapest48th cheapest48th cheapest49th cheapest49th cheapest49th cheapest50th cheapest50th cheapest50th cheapest

Say Amazon shows 48 phone cases a page.

So it might send ORDER BY price LIMIT 48.

So the 48 cheapest cases come back, the cheapest on top.

And what if the query leaves out ORDER BY?

SELECT name, xpFROM usersLIMIT 3;resultnamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75result, next timenamenamexpxpAda120Ada120Ada120Ben40Ben40Ben40Cy0Cy0Cy0Dee300Dee300Dee300Eli40Eli40Eli40Fay75Fay75Fay75

SELECT name, XP FROM users LIMIT 3 still sends back three rows.

But with no order promised, they can be any three.

Dee, Ben and Fay one time, and Cy, Dee and Eli the next.

Example: Just the first few rows

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users ORDER BY xp DESC, name LIMIT 3;.

Which three learners come back?

Explanation

First, ORDER BY sorts them most XP first: Dee, Ada, Fay, then Ben, Eli and Cy.

Then, LIMIT 3 keeps only the first three rows.

Therefore, Dee, Ada and Fay come back.

Question 1

Simplified's users table holds 2,000,000 learners. Its server sends Postgres SELECT name FROM users ORDER BY xp DESC LIMIT 2;.

How many rows does the server get back?

rows

Question 2

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users ORDER BY xp LIMIT 3;.

Which learners come back?

Question 3

Say Spotify keeps its songs in a Postgres table, songs, with a plays column. Its server sends Postgres SELECT title FROM songs LIMIT 50;, with no ORDER BY.

What does it send back?

Question 4

Simplified's analytics screen lists learners two at a time, fewest XP first.

Which query sends back just the first two?

Question 5

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users ORDER BY xp ASC LIMIT 1;.

Who comes back?

Question 6

Say Duolingo keeps a league's learners in a Postgres table, league, with an xp column: Mia has 410 XP, Leo 980, Ana 650 and Sam 120. Its server sends Postgres SELECT name FROM league ORDER BY xp DESC LIMIT 1;.

Who comes back?

Assistant

Highlight lesson text and click Ask AI, or type a question below.