DISTINCT Windows

Kevin Feasel

2016-04-26

T-SQL

Daniel Hutmacher looks at ways to get windows of distinct elements from a table:

Probably the most common distinct aggregate is COUNT(DISTINCT). In some respects, it’s similar to the windowed function DENSE_RANK(). DENSE_RANK(), as opposed to ROW_NUMBER(), will only increment the row counter when the ordering column(s) actually change from one row to the next, meaning that we can use DENSE_RANK() as a form of windowed distinct count

This is a very interesting approach to the problem.  Read the whole thing.

Related Posts

COUNT And NULL

Bert Wagner explains some of the trickiness of COUNT and NULL values in SQL Server: One thing I see fairly often (and am occasionally guilty of myself) is using COUNT(DISTINCT) and DISTINCT interchangeably to get an idea of the number of unique values in a column. While they will sometimes give you the same results, […]

Read More

Fun With QUOTENAME

Louis Davidson shares some tips on using the QUOTENAME function: Or if you are Rob Volk (@sql_r on Twitter), and you want to create an annoying database on your best frenemy’s SQL Server that includes brackets in the name, like: This [database] Is Awesome You will need to do: CREATE DATABASE [This [database]] Is Awesome]; […]

Read More

Categories

April 2016
MTWTFSS
« Mar May »
 123
45678910
11121314151617
18192021222324
252627282930