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.
dataprediction.wordpress.com 