CODEWITHSUNDEEP
t-sqlsql-order-bycasepartition
DeveloperM
DeveloperM

Reputation: 1239

derived column in Order by in partition

Using Sql Server 2008 R2.

Where there is more than 1 row of type demographic change, I need to delete all but 1 per person, but the types of demographic changes are weighted, with some more important than others. I don't know what the data will hold but if a more important one exists for a particular Contact, I want it to rise to the top.

I tried:

;WITH cte AS
(
    SELECT lastname, firstname, FieldChanged,
     Case 'FieldChanged'
     When 'firstname' then 0
     When 'lastname' then 0
     When 'ssn' then 1
     When 'xyz' then 5
       End as "Weight"
     , ROW_NUMBER() OVER (PARTITION BY D2.ContactId, D2.ContractId ORDER BY weight asc) AS demorow
    FROM MyDATA d2
    where d2.FieldChanged in ('firstname', 'lastname', 'ssn', 'xyz')
)
SELECT *
FROM cte 
WHERE demorow > 1

This gives me an error: Invalid column name 'weight'. I think I can't use APPLY since there's no unique key in the source table, which is not under my control.

Update:

CREATE TABLE dbo.MyTempTable

(firstname varchar(25) NOT NULL, lastname varchar(25) NOT NULL, FieldChanged varchar(25), ContactId uniqueidentifier, ContractId uniqueidentifier ) GO Insert into dbo.mytemptable (firstname , lastname , FieldChanged , ContactId , ContractId) Values ('john', 'smith', 'ssn', '688CB150-C7FD-E511-8709-00155D070201', '688CB150-C7FD-E511-8709-00155D070202') , ('john', 'smith', 'xyz', '688CB150-C7FD-E511-8709-00155D070201', '688CB150-C7FD-E511-8709-00155D070202') , ('mary', 'doe', 'xyz', '688CB150-C7FD-E511-8709-00155D070203', '688CB150-C7FD-E511-8709-00155D070202') , ('mary', 'doe', 'firstname', '688CB150-C7FD-E511-8709-00155D070203', '688CB150-C7FD-E511-8709-00155D070202') , ('mary', 'doe', 'lastname', '688CB150-C7FD-E511-8709-00155D070203', '688CB150-C7FD-E511-8709-00155D070202') , ('mary', 'doe', 'ssn', '688CB150-C7FD-E511-8709-00155D070203', '688CB150-C7FD-E511-8709-00155D070202')

For this data I'd want John Smith's and Mary Doe's respective xyz rows to be selected, as less important than their name change rows.

Update 2:

I think this works:

;WITH cte AS

( SELECT lastname, firstname, FieldChanged, Case FieldChanged When 'firstname' then 0 When 'lastname' then 0 When 'ssn' then 5 When 'xyz' then 1 else 9 End as "Weight", ContactId, ContractID FROM edi..MyDATA d2 where d2.FieldChanged in ('firstname', 'lastname', 'ce_ssn', 'Policy Number') ), cte2 As ( SELECT * , ROW_NUMBER() OVER (PARTITION BY ContactId, ContractId ORDER BY weight asc) AS demorow FROM cte ) SELECT * FROM cte2 WHERE demorow > 1

Upvotes: 1

Views: 439

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56745

Column aliases are assigned after all of the other clauses of a SELECT expression are executed (except for ORDER BY clauses, but not ORDER BY expressions), so you cannot use them within the same SELECT expression, only outside of them (or in an ORDER BY clause).

Here's a quick fix:

;WITH cte AS
(
    SELECT lastname, firstname, FieldChanged,
     Case FieldChanged
     When 'firstname' then 0
     When 'lastname' then 0
     When 'ssn' then 1
     When 'xyz' then 5
       End as "Weight",
    ContactId, ContractID
    FROM MyDATA d2
    where d2.FieldChanged in ('firstname', 'lastname', 'ssn', 'xyz')
),
cte2 As
(
    SELECT *
     , ROW_NUMBER() OVER (PARTITION BY ContactId, ContractId ORDER BY weight asc) AS demorow
    FROM cte
)
SELECT *
FROM cte2
WHERE demorow > 1

Upvotes: 1

Dharmendar Kumar 'DK'
Dharmendar Kumar 'DK'

Reputation: 2102

replace "weight" in the order by with the full CASE statement. Or put the main query (without order by) in a sub query and the row number in the outer query. YOu should then be able to access the "weight" column in order by.

Upvotes: 1

Related Questions