• tomcoffing

Azure Synapse – Advanced ORDER BY


This blog comes from the Azure Synapse Architecture and SQL book by Tom Coffing and David Cook.


Yes, the Azure Synapse Architecture and 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 all aspects of Azure Synapse. 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/services-9.


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. Stanley Johnson is in the first row with a class_code of null because Synapse 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?


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 dark mode. The Chameleon changes colors for the environment you desire.


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 Chameleon in Sherwood dark green mode.


Get Your Analytics for Free for all Databases


Analytics on Nexus is the best compliment for Analytics with Azure Synapse, 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 Synapse 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, then Nexus saves you 80 queries; you would have to pay a cloud vendor.


Check out the Nexus Garden of Analytics in this YouTube video and save lots of money.

https://www.youtube.com/watch?v=dMsAghFbYXk&t=20s.


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 Azure Synapse with Excel, Microsoft Access, Teradata, Oracle, SQL Server, DB2, Postgres, MySQL, Amazon Redshift, and Vertica, Snowflake, BigQuery, or other Synapse warehouses.


Check out the Nexus Super Join Builder in this YouTube video and become super at joins. And yes, you can edit the SQL Nexus creates.

https://www.youtube.com/watch?v=ARwo9prucw0.


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 Synapse 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.

84 views0 comments