Skip to main content
Share Your Experience: Take the 2024 Developer Survey
23 votes

Using DISTINCT in window function with OVER

This gives the distinct count(*) for A partitioned by B: dense_rank() over (partition by B order by A) + dense_rank() over (partition by B order by A desc) - 1
Ben's user avatar
  • 239
22 votes
Accepted

SQL counting distinct over partition

This is how I'd do it: SELECT * FROM #MyTable AS mt CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc FROM #MyTable AS mt2 WHERE mt2.Col_A = mt....
Erik Darling's user avatar
  • 41.1k
20 votes
Accepted

Using window function to carry forward first non-null value in a partition

The following query achieves the desired result: select *, first_value(somevalue) over w as carryforward_somevalue from ( select *, sum(case when somevalue is null then 0 else 1 end) over (...
maxTrialfire's user avatar
  • 1,094
18 votes

Form groups of consecutive rows with same value

1. Window functions plus subqueries Count the steps to form groups, similar to Evan's idea, with modifications and fixes: SELECT id_type , min(date) AS begin , max(date) AS end , count(*...
Erwin Brandstetter's user avatar
16 votes
Accepted

Performance comparison between using Join and Window function to get lead and lag values

The relatively low row-mode performance of LEAD and LAG window functions compared with self joins is nothing new. For example, Michael Zilberstein wrote about it on SQLblog.com back in 2012. There is ...
Paul White's user avatar
  • 85.8k
16 votes
Accepted

What is the difference between `ORDER BY` and `PARTITION BY` arguments in the `OVER` clause?

The PARTITION BY works as a "windowed group" and the ORDER BY does the ordering within the group. However, because you're using GROUP BY CP.iYear, you're effectively reducing your window to just a ...
Daniel Hutmacher's user avatar
13 votes
Accepted

Carry over long sequence of missing values with Postgres

Form groups with the window function count() and then take the first value in each group: SELECT foo_label , first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) AS ...
Erwin Brandstetter's user avatar
13 votes
Accepted

Adding a ROW_NUMBER() with no column to ORDER BY?

The canonical way to do this is the following: ROW_NUMBER() OVER(ORDER BY (SELECT NULL)). If you're golfing, you might try something like this: SELECT value, n = ROW_NUMBER() OVER(ORDER BY (SELECT 1))...
Joe Obbish's user avatar
  • 32.2k
13 votes
Accepted

How to get the last not-null value in an ordered column of a huge table?

A common solution to this type of problem is given by Itzik Ben-Gan in his article The Last non NULL Puzzle: DROP TABLE IF EXISTS dbo.Example; CREATE TABLE dbo.Example ( id integer PRIMARY KEY, ...
Paul White's user avatar
  • 85.8k
13 votes
Accepted

Simplifying Function with Recursive CTE and/or Window Function

You need an extra column to carry along the running total (fiddle). In the recursive part of the CTE below R refers to the "previous" row and A the current one so referencing the column from R is ...
Martin Smith's user avatar
  • 84.8k
12 votes

MySQL and window functions

It really should be noted that MariaDB 10.2 (released in 2017 May) has window functions. That's certainly one avenue to pursue if you need MySQL and window functions.
chx's user avatar
  • 427
12 votes
Accepted

Solving "Gaps and Islands" with row_number() and dense_rank()?

So the trick here is a property of two equally incrementing series which produce a difference that can be used to identify islands {11,12,13} - {1,2,3} = {10,10,10}. This property isn't enough to ...
Evan Carroll's user avatar
  • 63.5k
12 votes
Accepted

How to select the set of last non-NULL values per column over a group?

Using the concatenation technique from The Last non NULL Puzzle by Itzik Ben Gan would look like this with your sample table and column data types. select T.cat, cast(substring( ...
Mikael Eriksson's user avatar
11 votes

COUNT(*) OVER ()

COUNT(*) OVER () is one of those operations that sounds like it ought to be cheap for the query optimizer. After all, SQL Server already knows how many rows are returned by the query. You're just ...
Joe Obbish's user avatar
  • 32.2k
11 votes

Optimize Subquery with Windowing Function

When and if you are able to upgrade from SQL Server 2012 to SQL Server 2016, you may be able to take advantage of the much improved performance (especially for frameless window aggregates) provided by ...
Paul White's user avatar
  • 85.8k
11 votes
Accepted

Set non-negative floor for rolling sum, in PostgreSQL

This is how I solved a similar problem on Teradata using nested OLAP-functions: SELECT dt.*, -- find the lowest previous CumSum < 0 -- and adjust the current CumSum to zero Max(...
dnoeth's user avatar
  • 4,196
10 votes
Accepted

Row by row subtraction with single input number

Fundamentally, this is a running total problem: for each row you are getting the running total of qty and subtracting a fixed value from it. More specifically, this is about how you want to use the ...
Andriy M's user avatar
  • 23k
10 votes
Accepted

Sort table rows and save the row numbers using UPDATE

Join to a subquery that computes numero with the window function row_number(): UPDATE movimientos m SET numero = sub.rn FROM (SELECT id, row_number() OVER (ORDER BY orden, id) AS rn FROM ...
Erwin Brandstetter's user avatar
10 votes

How to get the last not-null value in an ordered column of a huge table?

I expected t-sql to optimize it out - on a block/record level, the task to do is very easy and linear, essentially a for loop ( O(n) ). That's not the query that you wrote. It may not be equivalent ...
Joe Obbish's user avatar
  • 32.2k
9 votes
Accepted

Understand why rank() over doesn't fit to don't select duplicates rows

RANK() is a deterministic function, meaning that duplicates will be labelled with the same rank value. The outputs of your queries suggests to me that there are multiple records with the same user_id ...
Mayberg's user avatar
  • 402
9 votes
Accepted

Optimize Subquery with Windowing Function

I'm assuming there's no index, as you haven't provided any. Right off the bat, the following index will eliminate a Sort operator in your plan, which would otherwise potentially consume a lot of ...
Daniel Hutmacher's user avatar
9 votes

How to select the set of last non-NULL values per column over a group?

Just add a check for NULL in the partition will do SELECT DISTINCT cat, FIRST_VALUE(val1) OVER(PARTITION BY cat ORDER BY CASE WHEN val1 is NULL then 0 else 1 END DESC, t desc) AS ...
Kelvin's user avatar
  • 91
8 votes
Accepted

Form groups of consecutive rows with same value

For a few points, Don't call a non-temporary table tmp that just gets confusing. Don't use text for timestamps (you're doing that in your example we can tell because the timestamp didn't get ...
Evan Carroll's user avatar
  • 63.5k
8 votes

Optimize Subquery with Windowing Function

I'm just gonna throw the ol' Cross Apply out there: SELECT e.* FROM #Example AS e CROSS APPLY ( SELECT TOP 1 e2.UniqueDate AS maxUniqueDate FROM #Example AS e2 ...
Erik Darling's user avatar
  • 41.1k
8 votes
Accepted

Explain an OVER clause

ROW_NUMBER() is a ranking window function, and ranking window functions require a mandatory ORDER BY clause. If you try to write it without the ORDER BY you will get a syntax error. SELECT ...
SQLRaptor's user avatar
  • 4,098
8 votes

How to get the last not-null value in an ordered column of a huge table?

One method, by using OVER() and MAX() and COUNT() based on this source could be: SELECT ID, MAX(value) OVER (PARTITION BY Value2) as value FROM ( SELECT ID, value ,COUNT(value) OVER (...
Randi Vertongen's user avatar
8 votes
Accepted

Aggregate overlapping date intervals

Directly: WITH cte1 AS ( SELECT account_id, start_date the_date, 1 weight FROM Subscriptions UNION ALL SELECT account_id, end_date, -1 FROM Subscriptions ), cte2 AS ( SELECT ...
Akina's user avatar
  • 20k
7 votes
Accepted

Calculate 40 day moving average w.r.t to a field

It is not really clear from the question what is the role of the call_type_id column. I will ignore it until you clarify. Without window functions Here is a simple variant that doesn't use window ...
Vladimir Baranov's user avatar
7 votes

Form groups of consecutive rows with same value

You can do this as a simple subtraction of ROW_NUMBER() operations (or if your dates are not unique, though still unique per id_type, then you can use DENSE_RANK() instead, though it will be a more ...
ErikE's user avatar
  • 4,305
7 votes
Accepted

Sum by Implicit Group

The "implicit" group you mention seems to be based on the row order. Unlike a spreadsheet or text file, a relational table is logically an unordered set of rows regardless of whether it is stored as ...
Dan Guzman's user avatar
  • 28.3k

Only top scored, non community-wiki answers of a minimum length are eligible