[sql] When to use Common Table Expression (CTE)

One of the scenarios I found useful to use CTE is when you want to get DISTINCT rows of data based on one or more columns but return all columns in the table. With a standard query you might first have to dump the distinct values into a temp table and then try to join them back to the original table to retrieve the rest of the columns or you might write an extremely complex partition query that can return the results in one run but in most likelihood, it will be unreadable and cause performance issue.

But by using CTE (as answered by Tim Schmelter on Select the first instance of a record)

WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
    FROM myTable 
)
SELECT * FROM CTE
WHERE RN = 1

As you can see, this is much easier to read and maintain. And in comparison to other queries, is much better at performance.

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to common-table-expression

SQL Server CTE and recursion example getting "No column was specified for column 2 of 'd'" in sql server cte? Update records in table from CTE How to create Temp table with SELECT * INTO tempTable FROM CTE Query Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable? The maximum recursion 100 has been exhausted before statement completion When to use Common Table Expression (CTE) Combining INSERT INTO and WITH/CTE Keeping it simple and how to do multiple CTE in a query Can you create nested WITH clauses for Common Table Expressions?