Google BigQuery – Advanced ORDER BY
This blog comes from the Google BigQuery SQL book by Tom Coffing and David Cook.
Yes, the Google BigQuery SQL book will soon be 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 hundreds of wonderful examples, covers every single SQL command, and will be your forever guide to mastering BigQuery SQL. 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/bigqueryclass.
In the example below, we order the data with an ORDER BY class_code statement at the end of the SQL. Therefore the answer set sorts by class_code in ascending mode because ASC is the default. Stanley Johnson is in the first row with a class_code of null because BigQuery sorts 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 class_code, but we add the NULLS LAST statement. The null value appears last in our answer set, but the data still sorts in ascending mode.
This example uses the Nexus Query Chameleon in dark mode. The Chameleon changes colors for the environment you desire.
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. I have taught many students who did not know how to order the data in that logical order. Therefore, I will show you how 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 (Vertica, Snowflake, and Google BigQuery). Each system query window and result gets a tab the same color the user chooses for the system.
Get Your Google BigQuery Analytics for Free
Analytics on Nexus is the best compliment for Analytics with Google BiqQuery, Snowflake, Redshift, and all systems. Imagine if you need to get dozens of analytic reports on a specific data set. Of course, you might have to run different analytic queries on BigQuery 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 Google.
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 Google BiqQuery with Excel, Microsoft Access, Teradata, Oracle, SQL Server, DB2, Postgres, MySQL, Amazon Redshift, and Azure Synapse, Snowflake, or other BigQuery warehouses or projects.
Check out the philosophy of the Super Join Builder in this YouTube video here.
Why Buy a Different Tool for each database when Nexus queries them all?
It took 18 years of development led by Tom Coffing to build the Nexus Query Chameleon. And the best part is that Nexus can query all systems in your enterprise and has a Super Join Builder that builds the SQL automatically as the user's point-and-click. And the most amazing part is that Nexus can easily join BigQuery tables with Excel, Access, and tables and views from all other database platforms. Check out just some of the amazing features of Nexus here. https://www.youtube.com/watch?v=o7YGXfxrJOM&t=24s.