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?

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
``````
Source (Stackoverflow)