An Overview of PostgreSQL Window Functions (2023-02-24)

SQLServerCentral Newsletter for February 24, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [An Overview of PostgreSQL Window Functions]( - [Getting started with SSIS External Configuration File]( - [Join Redgate at SQL Bits, March 15-18]( - [From the SQL Server Central Blogs - Patch your SQL Server instance today]( - [From the SQL Server Central Blogs - The Baseline for Flyway]( - [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Question of the Day - [Moving the PVS]( The Voice of the DBA  Daily Coping Tip Be gentle with someone you feel inclined to criticize 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. The Top 5 Technologists There's a common party question about which [5 people would you invite to a dinner party](? Often this is amended to include living or dead people, and it's often interesting to hear people tell you who they'd invite and why. Since most of the people reading this work in technology in some way, I was wondering who you would invite to a party that's related to technology. Living or dead, however tangential, is there a list you can come up with? I'll give you my list and then a few thoughts. For the list, I'll say [Steve Wozniak](, [Sal Khan](, [Reed Hastings](, [Gladys West](, and [Lawrence Lessig](. Maybe not all technologists per se, but all had an influence on my life using technology. Steve Wozniak because he was a geek hero and he seems like a fun person. Someone that enjoys technology. I had an Apple II early on and always wanted to meet him. Salman Khan because I think his use of technology to teach others, for free, was incredible. Education is something that's a big part of my life, and I appreciated his lessons helping my kids at times. I think someone that sees the world in this way would be a great conversationalist. Reed Hastings has been an incredible businessman in the tech world. He founded Pure Software and Netflix, and has been on the boards of Microsoft and Facebook. He has had an influence on education efforts in California. I would love to listen to him talk about business and technology. Gladys West is a mathematician who worked on a number of topics that built computer models for submarines and analyzing satellite data. She has had quite an influence on the GPS system many of us use every day in our phones. I'd love to know what it was like for someone like her to work and grow in our field. Not least, just last. Lawrence Lessig is a professor of law, and someone that has worked hard to ensure our legal world better represents fairness and ethics in technology. I've read a number of his books and would be interested in talking about how we ought to view the digital world from a fair rights perspective. That's my list. What's yours? Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [An Overview of PostgreSQL Window Functions]( rohind from SQLServerCentral This article looks at Window functions and how they can be used to solve problems in a different way from standard aggregate functions. [External Article]( [Getting started with SSIS External Configuration File]( Additional Articles from MSSQLTips.com This article illustrates how to use an SSIS external configuration file to enable the properties of package objects to be dynamically updated at run time. [External Article]( [Join Redgate at SQL Bits, March 15-18]( Additional Articles from Redgate From castles and dragons to swordfights and spells, this year’s SQL Bits will have it all. Make sure you don’t miss out on our great sessions, from Code Errors to Reliable Releases, to adding some PostgreSQL skills to your repertoire. And come say “hi” to us at the Redgate booth, where we’ll have some news for you. Get 10% off registering with 10REDGATE. [Blog Post]( From the SQL Server Central Blogs - [Patch your SQL Server instance today]( Randolph West from Born SQL with Randolph West On 14 February 2023, Microsoft released updates for all supported versions of SQL Server in the form of a General Distribution Release (GDR). A GDR is an out-of-band update... [Blog Post]( From the SQL Server Central Blogs - [The Baseline for Flyway]( Steve Jones - SSC Editor from The Voice of the DBA In my previous post, I set up the Flyway Desktop projects for SQL Server and PostgreSQL. I also added a table to each platform for development. In this post,... [Pro Encryption in SQL Server 2022]( [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Additional Articles from SQLServerCentral This in-depth look at the encryption tools available in SQL Server shows you how to protect data by encrypting it at rest with Transparent Data Encryption (TDE) and in transit with Transport Level Security (TLS). You will know how to add the highest levels of protection for sensitive data using Always Encrypted to encrypt data also in memory and be protected even from users with the highest levels of access to the database. The book demonstrates actions you can take today to start protecting your data without changing any code in your applications, and the steps you can subsequently take to modify your applications to support implementing a gold standard in data protection.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Moving the PVS What do I need to do if I want to move the Persistent Version Store (PVS) to a new filegroup? 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) Types of Always Encrypted What are the types of encryption available with Always Encrypted? Answer: Deterministic and Random Explanation: Always Encrypted supports Random and Deterministic encryption. Ref: Always Encrypted - [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 2017 - Development [How to get the transaction mode of a spid.]( - Hi All, Which DMV will tell me the transaction mode of a SPID? I did check MSDN but couldn't get the answer. - Autocommit transaction - Implicit transaction - Explicit transaction - Batch-scoped transaction - Distributed transaction Regards, Sam SQL Server 2016 - Development and T-SQL [Prompt user for input value]( -  Hey folks. I need to be able to have a little box or similar pop up so a user can enter a value, that will be part of the SELECT statement. I have looked everywhere and have had no luck. Oracle has this option I believe with the &. This works in Oracle: this […] SQL 2012 - General [query results of last full month's data]( - Thanks in advance for any help. I've found similar topics but not quite sure how to do this one. I need at any given time to return the results of the last full month's data regardless of how many days are in that month, e.g. today is February 20, 2023 and I want to return […] [Arithmetic overflow error converting expression to data type smallint. The state]( - hi, I have started getting this message, from a stored procedure, which is not repeating, it comes after now and then, or sometimes it does not come at all. (while testing: I kept stored procedure and parameter same). Msg 8115, Level 16, State 2, Procedure uspMS, Line 11 [Batch Start Line 0] Arithmetic overflow error […] SQL Server 2019 - Development [Us the DECRYPTBYPASSPHRASE function]( - I have a T-SQL script which encrypts the SSN using the ENCRYPTBYPASSPHRASE function with a pass phrase. ENCRYPTBYPASSPHRASE('Key', CAST(e.ssn AS nvarchar(100))) The ENCRYPT and DECRYPT function works fine when performed on the same server, which is SQL 2019. But, when I perform the ENCRYPT on server "A" . Then write the result set to server […] SQL Server 2008 - General [Login Failed. The login is from an untrusted domain and error 18452]( - Hello People, I found information over the internet and went step by step to check each setting but i surrender myself and decided to post here regarding this issue which i am facing. Login Failed. The login is from an untrusted domain and cannot be used with Windows authentication. Microsoft SQL Server, Error: 18452 The […] T-SQL (SS2K8) [BULK INSERT PROBLEM for CAV file]( - Hello SQL Server Community, I'm not new to T-SQL but I have a simple problem that I have not been able to solve after several hours of research. I have several CSV files that I want to import into SQL Server (v2008 R2) I have created a temp table. CREATE TABLE DataTMP ( Col1 varchar(255) […] [BULK INSERT PROBLEM for CAV file]( - Hello SQL Server Community, I'm not new to T-SQL but I have a simple problem that I have not been able to solve after several hours of research. I have several CSV files that I want to import into SQL Server (v2008 R2) I have created a temp table. CREATE TABLE DataTMP ( Col1 varchar(255) […] SQL Azure - Development [Advanced grouping for gaps and islands]( - Hi, I'm trying to figure out how to group a set of data based on how many days a person is sick. The problem is a gaps and island issue combined with running streak, with a twist. The records are either of type Work or Sick and are only saved on it's registered date. So […] Azure Data Factory [Best Course to prepare for GCP Cloud Security Engineer certification?]( - Hey there, I want to get GCP Cloud Security Engineer certification, but I'm a bit overwhelmed by the number of courses offered by various web academies. Can you recommend some top picks? Reporting Services [SSRS query builder, generated SELECT statement causing error]( - I'm working on a mysql query using the Visual Studio tableAdapter Query Builder, so far i'm getting this message (see image). Although the Query Builder executes the data given but cannot generate the columns needed in my dataset. I want to know what i'm doing wrong when my query works fine on my code. I'm […] General [Informatica SQL generating basic statement]( - I'm newbie using Informatica Data Quality Analyst  and am unable to create a basic SQL statement. The Sql query is being created against a mapping specification of a table that was imported as a flat file originally. The statement is as follows: Select ColumnA, ColumnB FROM Table1 WHERE Table1.ColumnA = 'S' That SELECT.... FROM part […] SQL Server 2022 - Development [Schedule a PowerBI dataset refresh on the "quarter" hour ???]( - Hi all, I know very little about Power BI, but I am hopeful that someone here has enough knowledge to know whether or not it is even possible to schedule a dataset refresh on the quarter-hour instead of merely on the half-hour or top of the hour.  I know that with SSRS, I can use […] [Hints for searching text?]( - I've got a table with like 6-7 million rows. Sometimes I'd like to search for certain text in a few columns. I can't index those columns because of text-length so I've to figure something out (because right now I can go on a really long coffebreak while doing a search). Searching using EQUAL is okay: […] [Query]( - Dear All My table contains the following records create table #ProductDetails(ProductID int , PurchaceDate datetime ,LicenceQty Int) insert into #ProductDetails values (1,getdate()-5,10) insert into #ProductDetails values (1,getdate()-4,25) insert into #ProductDetails values (2,getdate()-4,25) insert into #ProductDetails values (6,getdate()-3,100) insert into #ProductDetails values (6,getdate()-4,20) insert into #ProductDetails values (6,getdate()-5,20) select * from #ProductDetails In the above table, […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -