Jordan1200 Jordan1200 - 4 months ago 15
SQL Question

Find the common value over partition

I have a table like this :

Loan_Num asset LTV
1 20 0.2
2 20 0.2
3 20 0.12
4 20 0.2
5 10 0.3
6 10 0.3
7 10 0.22
8 10 0.3


And I want to add a common value to this table by the group of asset.

Loan_Num asset LTV cV
1 20 0.2 0.2
2 20 0.2 0.2
3 20 0.12 0.2
4 20 0.2 0.2
5 10 0.3 0.3
6 10 0.3 0.3
7 10 0.22 0.3
8 10 0.3 0.3


Any suggestions how to do this? is there a built in function for common value?

Answer

One way of doing this would be

WITH CTE1
     AS (SELECT *,
                COUNT(*) OVER (PARTITION BY [asset], [LTV]) AS C
         FROM   YourTable),
     CTE2
     AS (SELECT *,
                RANK() OVER (PARTITION BY [asset] ORDER BY C DESC, [LTV] DESC) AS R
         FROM   CTE1)
SELECT [Loan_Num],
       [asset],
       [LTV],
       MAX(CASE
             WHEN R = 1
               THEN [LTV]
           END) OVER (PARTITION BY [asset]) AS cV
FROM   CTE2 

Demo

Though actually this would be slightly more efficient as it removes a sort

WITH CTE1
     AS (SELECT *,
                COUNT(*) OVER (PARTITION BY [asset], [LTV]) AS C
         FROM   YourTable),
     CTE2
     AS (SELECT *,
                MAX(C) OVER (PARTITION BY [asset]) AS MaxC
         FROM   CTE1)
SELECT [Loan_Num],
       [asset],
       [LTV],
       MAX(CASE
             WHEN C = MaxC
               THEN [LTV]
           END) OVER (PARTITION BY [asset]) AS cV
FROM   CTE2 
Comments