SQL Server Reference

Queries beyond SELECT *.

Useful SQL Server patterns for finding, ranking, comparing and cleaning real application data.

Return the newest row for each customer

ROW_NUMBER + CTE

With RankedClaims As
(
    Select
        ClaimId,
        CustomerId,
        ReceivedDate,
        Row_Number() Over (Partition By CustomerId Order By ReceivedDate Desc) As RowNumber
    From Claims With (NoLock)
)
Select
    ClaimId,
    CustomerId,
    ReceivedDate
From RankedClaims
Where RowNumber = 1
Order By CustomerId;

Find duplicate values

GROUP BY / HAVING

Select
    DocumentNumber,
    Count(*) As RecordCount
From ClaimDocuments With (NoLock)
Group By DocumentNumber
Having Count(*) > 1
Order By RecordCount Desc;

Running total by date

Window aggregate

Select
    TransactionDate,
    Amount,
    Sum(Amount) Over
    (
        Order By TransactionDate
        Rows Between Unbounded Preceding And Current Row
    ) As RunningTotal
From Transactions With (NoLock)
Order By TransactionDate;

Conditional aggregation

Summarize multiple statuses

Select
    ProcessDate,
    Sum(Case When Status = 'Complete' Then 1 Else 0 End) As Completed,
    Sum(Case When Status = 'Error' Then 1 Else 0 End) As Errors,
    Count(*) As Total
From ProcessingLog With (NoLock)
Group By ProcessDate
Order By ProcessDate Desc;