Stairway to Advanced T-SQL Level 7: Ordering Your Data Using Ranking Functions (2022-07-06)

SQLServerCentral Newsletter for July 6, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Advanced T-SQL Level 7: Ordering Your Data Using Ranking Functions]( - [Why a leading bank with over 1,400 databases chose to think again about Redgate SQL Monitor]( - [Azure Synapse Link for SQL: From production to a data warehouse in a few clicks]( - [From the SQL Server Central Blogs - What is causing my constraint to be untrusted?]( - [From the SQL Server Central Blogs - Log in to Power BI Desktop as an External (B2B) User]( Question of the Day - [Azure Data Lake Storage Gen 2]( The Voice of the DBA  Daily Coping Tip Take a small step to overcome a problem or worry I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Be Smart An engineer at Google recently [claimed that one of the AI chatbots might have become sentient](. Great headlines, and whether true or not, this might bring some notoriety to the engineer.. It certainly did, and it also resulted in the engineer being suspended from his job. It's entirely possible this person might be fired. Perhaps I'm cynical, but I think the more talented he is, the more likely he keeps his job. Less talented, likely fired. This might not be fair, but I am a realist. The more value someone brings, the more tolerance for missteps. I give a talk on branding, and one of the things I do before giving you practical tips is to remind you to be cautious. A brand can be a very positive asset, but it can be a detriment as well. One of my stories is about Mark Jen, who Google fired after [he blogged a few things about his employment](. He was highly recruited and worked at Google for only a few days. I worked in a public company one time, in a large Operations group of about 20. We were listening to the earnings call one quarter when Security staff walked up and escorted a person 2 or 3 cubicles away from me out of the building. The returned to package up his stuff once he was gone. Apparently our boss told us this person had posted some of our earnings results while the call was going on, literally minutes (20 or 30) before they were announced. However, that's illegal and a violation of securities rules. Most of us know not to post passwords, IP addresses, or other sensitive infrastructure data on the Internet. Most of us should know not to post data, especially on a site like [SQL Server Central](. If we are looking for help with an issue, we need to mock up a situation. We might not be able to post code, and we might not be able to write about the specifics of our job in our blog. I've never had an issue with this in over 25 years of blogging and asking/answering questions on forums., but I've followed one rule. It's the same rule I heard one person say was their internal guidance for blogging. Be Smart. Don't post anything that might cause an issue. Whether you are in a forum or on your blog. Even inside your company, some information might be sensitive and compartmentalized. If you have a doubt, ask someone. It's that simple. Just ask someone who can give you a second opinion. I do this regularly with SQL Server information and Microsoft. There are times that I am unsure of something is under an NDA, so I ask. This includes words, pictures, and code. I do the same thing at Redgate, as sometimes things on our internal network might not be publicly released yet, so I just ask. On my personal accounts, I might ask my wife if I can post something before I do it. I've learned it's better to ask permission in these cases. I haven't always done that in my job. Sometimes rebooting a system or making a minor change without following every rule, but those have been cases where I had a very strong understanding of the situation and implications. It's not something I do lightly, and it's rare, but there are times to ask for forgiveness rather than permission. However when dealing with public disclosure of anything, I think you are smarter to ask permission first. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Advanced T-SQL]( [Stairway to Advanced T-SQL Level 7: Ordering Your Data Using Ranking Functions]( Greg Larsen from SQLServerCentral.com In SQL Server there are 4 different ranking functions: RANK, DENSE_RANK, NTILE, and ROW_NUMBER. These ranking functions were introduced in SQL Server 2005. In this stairway level I will be reviewing each of these different ranking functions, and will show you how to use them by providing a few examples. [External Article]( [Why a leading bank with over 1,400 databases chose to think again about Redgate SQL Monitor]( Additional Articles from Redgate Headquartered in Louisville, Kentucky, Republic Bank is one of the most innovative and forward-looking banks in the US, with 42 full-service banking centers across five states and assets of $6.2 billion. Find out why they chose Redgate SQL Monitor. [External Article]( [Azure Synapse Link for SQL: From production to a data warehouse in a few clicks]( Additional Articles from Redgate Microsoft announced Azure Synapse Link for SQL. In this article, MVP Dennes Torres explains the new feature. [Blog Post]( From the SQL Server Central Blogs - [What is causing my constraint to be untrusted?]( Zikato from StraightforwardSQL The problem In this scenario, you have discovered that one of your Check constraints or Foreign keys is not trusted. Maybe you’ve detected it with a sp_Blitz, dbachecks or out of... [Blog Post]( From the SQL Server Central Blogs - [Log in to Power BI Desktop as an External (B2B) User]( Meagan Longoria from Data Savvy I noticed Adam Saxton post a tip on the Guy in a Cube YouTube channel about publishing reports from Power BI Desktop for external users. According to Microsoft Docs...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Azure Data Lake Storage Gen 2 Azure Data Lake Storage Gen 2 is built on ...? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) The July Fourth Anniversary Which of these gives us number that represents today's anniversary of the Declaration of Independence? Answer: DATEDIFF (YEAR, '1776/07/04', GETDATE ()), SUM(2022-1776) Explanation: Either of these are correct: - SUM(2022-1776) - DATEDIFF (YEAR, '1776/07/04', GETDATE ()) Happy Fourth of July in the US. For everyone else, Happy Monday.  [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2016 - Administration [Shrink Database or Create Empty Database after 4TB free space]( - Hello! We have 5 TB database which contains BLOB about 4TB. We have exported the images to file system, now we need to update the column (which contains the BLOB data) with "NULL" values. How long will the update process take to update that column to NULL values? Once updated,DB will have about 4 TB […] SQL Server 2016 - Development and T-SQL [Questions about SQL Server 2016 (and later) High Availability Groups (HADr)]( - I'm looking into implementing availability groups for our organization after we upgrade from SQL Server 2008 R2 to SQL Server 2016 to replicate several copies of our databases. Does anyone have experience setting this up? I have a few questions.... If a SQL database is corrupted (Encrypted by a ransomware virus, for instance), what does […] [How To Flatten JSON Data?]( - I receive data in JSON files and for one particular type of data I am having problems getting it into a sensible SQL format. The data consists of an ID and a JSON array of bank accounts belonging to that ID. For the data below, I want one row for ID 4710592 with the data […] [Last Cube Refresh Showing Wrong Time]( - Hi I have a tabular model and have been asked to retrieve the last cube refresh time. However when I look in $SYSTEM.MDSCHEMA_CUBES the time is different to when I right click on the model database and look at the properties. The time in the properties is correct but in $SYSTEM.MDSCHEMA_CUBES it is showing in […] [Create Multiple XML Files using SSIS]( - Hi community, I have a table in my database which consists of 100,000 rows. I am trying to move this data to 10 XML files (10,000 each rows). I am able to convert my data to XML format using ZS Export XML Task. I basically want my XML files like this: Individual_1.xml, Individual_2.xml, Individual_3.xml and […] SQL 2012 - General [Server related questions]( - Hi, could someone answer the following questions please? I've googled of course but I'm still a little confused on a few SQL matters: I've downloaded SSMS and SQL express. I understand that SSMS is just a way of accessing a SQL database, while SQL express is the server. My question is why does SQL express […] [MS SQL Server 2012 question regarding SSIS]( - OK so I have a customer with a hosted website with eCommerce options. Essentially if you a good CC and the shipping address matches the address on the card, they will take your order. This order information is stored in a MySQL database that they log into via a website, where they proceed to physically […] SQL Server 2019 - Administration [SQL Server authentication issue with linked servers & mixed authentication types]( - I have a target server that I have read-only access to with Windows authentication. We'll call this Server01. I need to aggregate some data and do some analytics that isn't always easy in-session so I need to store some data more permanently than in temp tables. I also need to correlate with other data from […] [Performance problem]( - Hi I have one server running SQL server 2017 and one running SQL server 2019. The developer has reported that the SQL 2019 is much slower than the SQL 2017. Both servers are virtual and has the same spec regarding memory, CPU, OS ( Windows 2019). The settings in SQL is the same on the […] [AOAG Listener Error:Kerberos Status is showing: Incorrect username and Password]( - Hi, I am having error in creating AG Group Listener. We finally try to create one from Failover Cluster Manager by right click on AG role and added Listener from there. Listener IP comes online but Listener name still shows offline state. Please if anyone having same error, how to fix this. I am lost. […] SQL Server 2019 - Development [Updating a table from another table or a query.]( - I have a procedure that creates a table. One of the fields labeled delay is yes or no field.  I have two queries that i can either create two tables and use a field from there with a value of > 0 to put yes into delay field of the other table. I'm at loss […] [Cumulative count distinct for a period of 3 months]( - Hello Experts, I have this sample dataset that describes a transaction fact table called transaction: I need to write a query that gives this result: The main goal is to calculate the customers number distinct of 3 last months for each year, month. [Retrieving date from next from to fill in column on row above]( - Hi all, I have a very short query that contains an effective date but not expiration date. I would like to populate a expiration date set to 1 day before the next row's effective date. Here is query and results. For example, on part 100-121-0000, Revision A2; I'd like the Expiration Date to be 08-09-2009 […] [Getting count from Scheduled job procedures]( - I have a new requirement to create a Power BI Dashboard that shows current running jobs, average duration and a number of other KPIs including how many records are returned from a particular scheduled stored procedure. Is there an easy way to return this value? Most of the other data I can get from the […] General Cloud Computing Questions [What's your opinion about Google Cloud SQL?]( - Hi, I wonder what your opinion about Google Cloud SQL is! We are a small company running our MySQL server on one of our vServer and everything runs fine. But we have a few concerns about our project in the future: Backups - we did not implement them yet and I don't want to keep […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -