CODEWITHSUNDEEP
sqlsql-servert-sql
disassemble-number-5
disassemble-number-5

Reputation: 995

Find values from row where some column meets an aggregate condition?

SQL SERVER 2008 R2. Two tables having a 1-many relationship, and want some fields from both, but on the many side, only values from a certain row. That row is specified by some condition, in my case it should the row having the smallest ClaimId for that PaymentId.

IF OBJECT_ID('tempdb..#Payments') IS NOT NULL DROP TABLE #Payments
CREATE TABLE #Payments ( PaymentId int UNIQUE NOT NULL )
INSERT INTO #Payments ( PaymentId )
VALUES (1), (2), (3)

IF OBJECT_ID('tempdb..#Claims') IS NOT NULL DROP TABLE #Claims
CREATE TABLE #Claims ( PaymentId int NOT NULL, ClaimId int NOT NULL, FirstName varchar(50) NOT NULL )
INSERT INTO #Claims ( PaymentId, ClaimId, FirstName )
VALUES (1, 51,'Joe'), (1, 57,'Jane'), (2, 62,'Spot'), (2, 63,'Rover'), (3, 88,'Sue'), (3, 89,'Sally')

SELECT * FROM #Payments p
JOIN #Claims c ON p.PaymentId=c.PaymentId
WHERE c.ClaimId=MIN(ClaimId)
GROUP BY p.PaymentId
HAVING MIN(ClaimId)

The desired result is as follows, where the claim is from the row having the minimum claimId for a given paymentId. ClaimId may not be ordered, so seeking minimum, not first.

PaymentId   ClaimId   FirstName
1           51        Joe
2           62        Spot
3           88        Sue

I'd be happy to refer to an existing question/answer, but don't know how to word this that I find something similar. Perhaps Get field value from a record that causes an aggregate condition to be true but I didn't understand it.

Upvotes: 1

Views: 81

Answers (5)

McNets
McNets

Reputation: 10807

Both sentences return same result, first one select MIN(ClaimId) for every PaymentId:

SELECT P.*, C.*
FROM #Payments P
    INNER JOIN #Claims C ON C.PaymentId = P.PaymentId
WHERE C.ClaimID IN (SELECT MIN(ClaimID) OVER (PARTITION BY PaymentId) FROM #Claims);

Second uses a CTE to find MIN(ClaimId) before to join to Payments table:cº

WITH PY AS
(
    SELECT PaymentId, MIN(ClaimId) as ClaimID
    FROM #Claims
    GROUP BY PaymentId
)
SELECT PY.PayMentId, PY.ClaimId, C.FirstName, P.*
FROM PY
     LEFT JOIN #Claims c ON p.ClaimId = c.ClaimId;
     LEFT JOIN #Payments P ON P.PaymentId = PY.PaymentId


+-----------+---------+-----------+
| PayMentId | ClaimId | FirstName |
+-----------+---------+-----------+
| 1         | 51      | Joe       |
+-----------+---------+-----------+
| 2         | 62      | Spot      |
+-----------+---------+-----------+
| 3         | 88      | Sue       |
+-----------+---------+-----------+

Check it here: http://rextester.com/KRT45653

Upvotes: 1

KeithL
KeithL

Reputation: 5594

use windows function row_number to provide an order

;with cte as
(
select PaymentID, ClaimID, row_number() over (partition by PaymentID order by ClaimID ) as rn
from #Claims
)

select p.*,cte.ClaimID
from #Payments p
join cte on cte.paymentID = p.paymentID
where rn=1 --limits to earliest claimid

Upvotes: 1

Fuzzy
Fuzzy

Reputation: 3810

solution using RANK()

;WITH A
    AS (SELECT p.PaymentId
            , c.ClaimId
            , c.FirstName
            , rn = RANK() OVER(PARTITION BY P.PaymentId ORDER BY ClaimId ASC)
        FROM   #Payments p
             JOIN #Claims c ON p.PaymentId = c.PaymentId)
    SELECT PaymentId
        , ClaimId
        , FirstName
    FROM   A
    WHERE  rn = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

In SQL Server, I like to use outer apply for this:

select p.*, c.*
from #Payments p outer apply
     (select top 1 c.*
      from #Claims c
      where c.paymentid = p.paymentid
      order by c.claimid
     ) c;

Upvotes: 1

Aducci
Aducci

Reputation: 26674

You can use the ROW_NUMBER window function like this:

SELECT PaymentId, ClaimId, FirstName
FROM
(
    SELECT 
        p.PaymentId, 
        c.ClaimId, 
        c.FirstName, 
        ROW_NUMBER() OVER (PARTITION BY p.PaymentId ORDER BY c.ClaimId) as RN 
    FROM #Payments p
    JOIN #Claims c ON p.PaymentId=c.PaymentId
) as T
WHERE RN = 1

Upvotes: 2

Related Questions