Picture of Tom Coffing teaching back in the early 1990s where he helped get 10,000 people certified on their Teradata Certifications.
You may think you know the ORDER BY statement but put on your seatbelt because Tom Coffing is about to explain it like you never knew.
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 second row with a Grade_Pt of 0.00 because he has the lowest grade, but Stanley Johnson is in the first row because Teradata puts nulls first 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 LAST statement. The null value appears last 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 worst 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 has the worst students incorrectly 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 three queries running simultaneously (Snowflake, Google BigQuery, and Teradata). 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 Teradata 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 Your Teradata Analytics for Free
The best compliment for Analytics with Teradata 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 Teradata 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. Then, 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. So, for example, if you need 80 analytic reports, Nexus saves you 80 queries; you must pay Teradata.
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 Teradata with Excel, Microsoft Access, Snowflake, Oracle, SQL Server, DB2, Postgres, MySQL, Amazon Redshift, Azure Synapse, Google BigQuery, or other Teradata warehouses.
Check out this YouTube Video of the Super Join Builder.
This picture is the Nexus Super Join Builder joining three Teradata tables with two Snowflake tables.
Comentarios