Databases · Lesson 6.1
Putting rows in order
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.
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.
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.
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.
Tap Popular on a YouTube channel, and it might send ORDER BY views DESC.
So the most viewed video, Pizza dough, comes first.
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.
Open Spotify's Top 50, and it might send ORDER BY plays DESC.
So the song with the most plays, Espresso, comes first.
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.
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?
Databases · Lesson 6.2
Who comes first in a tie?
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.
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.
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.
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.
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.
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.
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?
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?
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?
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?
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?
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?
Databases · Lesson 6.3
Just the first few rows
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.
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.
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.
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.
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, 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.
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.
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?
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?
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?
Simplified's analytics screen lists learners two at a time, fewest XP first.
Which query sends back just the first two?
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?
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?