I found that if I can give a row numbering to only a selected few columns, and then order by that numbering, I would have the building blocks for a very powerful way to slice my data. I am indebted to a friend who introduced me to this technique. After first finding out about it, I researched it some more, and found there were many variants of it. There was, of course, a particular style that became my favourite.

Here is an example of my favourite, using AdventureWorks…

USE AdventureWorks;

GO

SELECT c.FirstName, c.LastName

,ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS ‘Row’

,s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson s

INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID

INNER JOIN Person.Address a ON a.AddressID = c.ContactID

WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

Another example of this used in a sub query. The power of this, is that I can filter out the top one in the inner sub-query for my outer query. In effect, this gives me a way of brining out exactly what I want within some groupings.

Select

*

From

(

Select

Row_Number() Over (Partition By CON.ContractID Order By PR.ActivationDate Desc) AS Row

,PR.ActivationDate

,CON.ContractID

,CON.LoanType

,CON.Clients As NameOfClients

,CON.ContractAmount As TotalAmount

,(CON.ContractAmount) + (CON.ContractAmount * CON.InterestRate) As TotalDue

,((CON.ContractAmount) + (CON.ContractAmount * CON.InterestRate)) – (CON.OutstandingCapital + CON.OutstandingInterest) As TotalAmountRepaid

,(CON.OutstandingCapital + CON.OutstandingInterest) As TotalAmountOutstanding

,MC.DescriptionNotes As ContractStatus

From

Contract CON

Inner Join PaymentReceived PR On CON.ContractID = PR.ContractID

Inner Join PropertyContract PC On CON.ContractID = PC.ContractID

Inner join Property PROP On PC.PropertyID = PROP.PropertyID

Inner Join CommercialProperty CP On PC.PropertyID = CP.PropertyID

Left Outer Join State STAT On CON.ContractID = STAT.ContractID And STAT.Date = ‘1900-01-01’

Left Outer Join MiscCodes MC On STAT.Status = MC.Code And MC.Group = ‘3’ And MC.Module = 9

Where

PROP.IsDeleted <> 1

And CP.IsDeleted <> 1

And MC.DescriptionNotes In (‘Council Registered’, ‘Registry Re-Applied’)

) As Tmp

Where Row = 1 And ActivationDate < ‘2016-04-01’

Order By ContractID, ActivationDate

The power of this is to drop the results into a table. And let that be a section of my manipulation, that I am building up modularly.

Advertisements