Amazon Redshift – Advanced ORDER BY
This blog comes from the Amazon Redshift Architecture and SQL book by Tom Coffing and David Cook.
Yes, the Amazon Redshift Architecture and SQL book is on its way to your favorite bookstores, such as Skillsoft, where you can read it online, or Amazon, where you can buy it. It is another masterpiece of information from Tom Coffing and David Cook, two of the most famous authors in the history of computers. In addition, you can purchase your PDF version of the book, which is in color, has wonderful examples of the architecture, covers every single SQL command, and will be your forever guide to mastering Redshift. The book is only $199.00 and comes with your name watermarked on each page. Companies can purchase the book for all of their employees for $2,500.00.
Contact Tom.Coffing@CoffingDW.com if you want to purchase the book. Check out the table of contents here. https://www.nexusdataserver.com/redshiftclass.
In the example below, we order the data with an ORDER BY grade_pt statement at the end of the SQL. Therefore the answer set sorts by grade_pt in ascending mode because ASC is the default. Michael Larkins is in the first row with a GRADE_PT of 0.00 because he has the lowest grade.
A big key to notice is that the null value is in the last row, so Redshift puts nulls at the end when sorting in ascending mode.
This example uses the Nexus Query Chameleon. Notice all of the systems in the systems tree on the left. Why have query tools for different systems when Nexus queries all systems in your enterprise?
One of the options with an ORDER BY statement is to use NULLS FIRST or NULLS LAST, which allows you to control where nulls appear. In the next example below, we still ORDER BY GRADE_PT, but we add the NULLS FIRST statement. The null value appears first in our answer set, but the data sorts in ascending mode.
This example uses the Nexus Query Chameleon in dark mode. The Chameleon changes colors for the environment you desire.
The NULLS LAST option can be particularly beneficial when combining the TOP command with an ORDER BY statement. For example, imagine you want to know the top three students in your student_table. To make that happen, you might run the TOP command with an ORDER BY GRADE_PT DESC.
In our example below, we run two commands using two query windows. The example on the left does not have the TOP 3 students correctly because the null value appears at the top. The example on the right uses the NULLS LAST option, and the data comes out as we desire.
This example uses the Nexus Query Chameleon with multiple query windows side by side.
In the example below, we attempt to order the data by CLASS_CODE, and the data sorts alphabetically. However, when I went to school, you were a freshman, then a sophomore, then a junior, and finally a senior. Therefore, most students can't order the data in that logical order. Therefore, I will show you multiple ways to do so.
This example uses the Nexus Query Chameleon in bookend mode, which places the answer sets to the right of the query window.
Many people don't know you can use CASE in your ORDER BY statement. The example below uses a CASE statement to logically sort the data as you went to high school or college.
This example uses the Nexus Query with four queries running simultaneously (Vertica, Snowflake, Google BigQuery, and Amazon Redshift). Each system query window and result gets a tab the same color the user chooses for the system.
You can also use an ORDER BY statement with a DECODE in Snowflake to accomplish the same logical order when sorting the answer set.
The DECODE is similar to the CASE but in a different format. We are ordering in the DECODE by CLASS_CODE, and if the value equals 'FR,' then give it a value of one, just like we did with the CASE statement in our previous example. Likewise, if the CLASS_CODE equals a 'SO,' give it a two, and so on. The five at the end is the ELSE statement in a DECODE.
Get All Amazon Analytics for Free
Amazon Redshift can save your company time and money. However, the best compliment for Analytics with Redshift is Analytics on Nexus. Imagine if you need to get dozens of analytic reports on a specific data set. You might have to run different analytic queries on Redshift to get your reports, but one of the best concepts of Nexus is the Garden of Analytics.
The Nexus saves every answer set you run during the day into the Garden of Analytics, which uses your PC's memory. Using drag and drop templates, anyone can get over 80 different analytic reports, graphs, charts, pivots, or spreadsheets, and Nexus does all of the calculations for free inside your PC. If you need 80 analytic reports, then Nexus saves you 80 queries; you would have to pay Redshift.
Check out the Nexus Garden of Analytics in this YouTube video and save lots of money.
Why Write the SQL When Nexus Writes It For You?
Nexus users can use the Super Join Builder, which shows you tables and views, connects you to which tables join, and writes the SQL automatically. If you want to see the greatest automated join builder worldwide, check out this YouTube Video. And yes, the Nexus Super Join Builder joins data from Amazon Redshift with Excel, Microsoft Access, Teradata, Oracle, SQL Server, DB2, Postgres, MySQL, Snowflake, and Azure Synapse, Google BigQuery, or other Amazon Redshift warehouses.
Check out the brilliance of the Super Join Builder working on all systems in this YouTube video: https://www.youtube.com/watch?v=ARwo9prucw0.