SQL #4: DISTINCT, Ordering, & Review

@grchab

#sql #learnsql #datascience #dataanalyst #dataentry #dataengineering #learninpublic

♬ original sound – Curiosity Corner 🙇🏽‍♂️

Next we have the DISTINCT function. Like I said, real-world data is messy, so you’re gonna have scattered entities and duplicates.

For my basketball example, I couldn’t really think of a case where you would see a duplicate of anything, so I’m gonna use sqlbolt’s example of Pixar movies. In this case, we want to see all the directors listed in the table in alphabetical order.

SELECT DISTINCT director FROM movies
ORDER BY director ASC;

So ‘movies’ would be the table name, and ‘director’ is the column we’re selecting from. Now in the table, the directors listed have multiple movies, for example, John Lasseter made Cars, Toy Story, and A Bug’s Life. So if we didn’t put the ‘DISTINCT’ command there, it would show his name 3 times. We don’t need to see that.

The ‘ORDER BY’ command is self-explanatory, and the ‘ASC’ at the end is for ascending. So this will show all the directors in alphabetical order from a-z. You could leave it out and it’ll still do it that way but it’s good to show it as a formality. If I wanted it listed z-a, or descending order I’d use the ‘DESC’ command, make sure you type the ‘e’. ASC for ascending, DESC for descending

Next, we want to see the last 4 movies that came out, ordered from newest to oldest.

SELECT title, year FROM movies
ORDER BY year DESC
LIMIT 4;

So obviously we want to see the title and the year. Since we want to see the most recent year, we use the descending order because the newest year would be the highest number, so it’s going down. And the ‘limit 4’ is self-explanatory.

Lastly, the ‘limit 4’ would only show the first 4 in the table. If I wanted to see the next 4, without seeing that first 4, I’d add an OFFSET command.

SELECT title, year FROM movies
ORDER BY year DESC
LIMIT 4 OFFSET 4;

The ‘OFFSET 4’ is what tells it to leave out that first 4. Of course if I wanted to see both I’d just do ‘LIMIT 8’

No we’re gonna review all we learned in a new table.

This next table is a list of cities. Its columns are the city name, its country, population, latitude, and longitude. We’ll name the table ‘na_cities’ as in ‘North American cities’.

First task. Let’s say I want to see all the US cities by their latitude from north to south

SELECT city, latitude FROM na_cities
WHERE country = "United States"
ORDER BY latitude DESC

Once again, SELECT command, ‘city’ and ‘latitude’ are the columns we want to view. The WHERE specifies US cities, otherwise it would show cities from all countries. And the ORDER BY sorts the latitude for us. Higher latitude is more north, so since I want to view from north to south, I’d use the DESCending order so it’ll start from the highest latitude.

Next, I want to see all cities that are west of Chicago from west to east

SELECT city, longitude FROM na_cities
WHERE longitude < -87.6298
ORDER BY longitude ASC;

Now with longitude, the higher number is more east. So since Chicago’s is negative 87, if I want to see what’s west of that I’d use the less than sign, so it’ll show all longitudes that are less than Chicago’s -87.

Again we want to see these cities from west to east. So from Chicago, we’re not starting with the closest city that’s west of it, we want the westernmost city, which means we use ascending order, starting from the lowest number. Again, the lower the longitude, the more west, so the city with the lowest longitude in this table is Los Angeles. Every city after that, with higher longitudes, is moving back east towards Chicago, because, I’ll say it one more time, we have to list these cities from west to east. I know this is anal but this stuff matters in a real-world scenario.

Next, I want to see the 2 largest cities in Mexico by population.

SELECT city, population FROM na_cities
WHERE country LIKE "Mexico"
ORDER BY population DESC
LIMIT 2;

SELECT and WHERE speak for themselves. I want it to start with the largest city, so we use DESCending order. And I only want to see the 2 largest so ‘LIMIT 2’

Lastly, I want to see the third and fourth largest US cities by population.

SELECT city, population FROM na_cities
WHERE country LIKE "United States"
ORDER BY population DESC
LIMIT 2 OFFSET 2;

First 3 lines have been gone over enough. Just remember, since I want to see the third and fourth cities on this list, I only want to see 2 cities, hence the ‘LIMIT 2’, and since it’s third and fourth, I do ‘OFFSET 2’ to cut off the first 2 cities, leaving the third and fourth.