Hello all,
I have a table and loan as follows:
Table: Loan
Fields:
LoanId, PoolId, RelID, RelSeq, Amount
I need a query that will do the following. I am trying to get a list of 'primary loans' for a set of data.
The way the data comes together is as follows:
First Data Scenario:
I have a PoolId such as '123' and it may have 6 loans as follows:
LoanId, PoolId, RelID, RelSeq, Amount
1 123 1123-1 1 100.00
2 123 1123-1 NULL 200.00
3 123 1123-1 NULL 300.00
In this case I know I have a primary loan based on there is more than one RelId same value and I know that LoanId = 1 is the
primary based on the RelSeq = 1 of this group.
Second Data Scenario:
LoanId, PoolId, RelID, RelSeq, Amount
4 123 1123-2 NULL 100.00
5 123 1123-2 NULL 200.00
6 123 1123-2 NULL 300.00
In this case I know I have a group with a primary based on there is more than one RelId same value but the RelSeq are all
NULL in this case I need to take the 'greatest' amount value as the Primary so in this case LoanId = 6 is my Primary. If for some reason the Amounts are all null or two the same Amount value just take the first.
I want to return a single result set with the two records there will be other records with RelID but not in a group just it's own
I don't want to return:
This would be my return set:
LoanId
1
6
Thanks all