“Whoever oppresses a poor man insults His maker,
but he who is generous to the needy honors Him.”
Main Source: (1) Practical SQL: A Beginner’s Guide To StoryTelling With Data by Anthony Debarros (2nd Edition). (2) The PostgreSQL Documentation.
However, this post HAS variations to the above-mentioned text.
PURPOSE
This PostgreSQL series builds upon the remaining posts. Please start at Post#2 to accurately follow along, as the current post assumes you have entered the former SQL statements to create tables & manipulate the data inside.
This post will focus on the ORDER BY & DISTINCT clauses to use in a SELECT statement with PostgreSQL.
The previous post focused on creating basic SELECT statements.
ORDER BY
Data in PostgreSQL can be put into a particular order with the ORDER BY clause.
The original data in our table will not change when we use the ORDER BY clause.
By default, ORDER BY sorts in ascending (ASC) order, though this can be changed to descend (DESC).
Try entering this code into your query:
SELECT * FROM administration ORDER BY last_name ASC;You should Output similar to this:
Question #1: How has the data inside our administration table changed?
After running our ORDER BY query, the data inside our administration table has become organized by the “last_name” column in Ascending order.
Originally, our table was ordered by “admin_id” number with “admin_id #1” at the top as follows:
Question #2: What is the current salary of the administrator with the “admin_id” of #3 ?
$51,000 !!! Bridgerton M. Lastburgerson from Marcus Bridge HS currently has a salary of $51,000.
Great Job!
Let’s try a different ORDER BY statement.
Let’s use one that limits our rows, to do that we need to remove the *asterick, and replace it with specific column names.
SELECT admin_id, first_name, last_name FROM administration ORDER BY last_name ASC;
Alternatively: We could use the number 3 to replace “last_name”.
SELECT admin_id, first_name, last_name FROM administration ORDER BY 3 ASC;Technically, because the default is to ascend, you could leave off the ASC at the end and get the same results.
SELECT admin_id, first_name, last_name FROM administration ORDER BY 3;Our output:
Question #3: How do you alter the code to organize the “last_name” column in descending order?
To organize the “last_name” column in descending order replace the keyword ASC with DESC.
SELECT admin_id, first_name, last_name FROM administration ORDER BY last_name DESC;
SORTING MORE THAN ONE COLUMN AT A TIME
What if we want to sort by “school_name” in ascending order & sort by ‘salary_current” in descending order at the same time?
Try this code:
SELECT * FROM administration ORDER BY school_name ASC, salary_current DESC;Your Output should be similar:
Question #4: (Part A)-What is the second highest salary for administrators at Lee County Middle School? (Part B)-What about the third highest salary?
(Part A)-What is the second highest salary for administrators at Lee County Middle School?
The second highest salary for administrators at Lee County Middle School is $41320.
(Part B)-What about the third highest salary?
The third highest salary for administrators at Lee County Middle School is $39855.
“The one who oppresses the poor person insults his Maker,
but one who is kind to the needy honors Him.”
USING DISTINCT STATEMENTS
The DISTINCT keyword gets rid of duplicate rows of data by fetching only unique values. DISTINCT may be handy for catching accidentally misspelled words & names when entering data values into our table.
The DISTINCT keyword comes after SELECT.
Try this code:
SELECT DISTINCT school_name FROM administration;Output:
Question #5: How would we write a similar PostgreSQL statement that puts the previous table (Table A) into Descending Order by the name of the school?
The previous code would need to updated with an ORDER BY clause with the DESC keyword after the “school_name” column.
SELECT DISTINCT school_name FROM administration ORDER BY school_name DESC;
Great Job!!!
WHAT’S NEXT ?!?
Post #8 will focus on: Using the WHERE clause to filter rows of data in our administration table.
“He that oppresseth the poor reproacheth his Maker:
But he that honoureth Him hath mercy on the poor”
Leave a Reply