We help IT Professionals succeed at work.
Get Started

SQL Syntax Help Group By type query

sbornstein2
sbornstein2 asked
on
181 Views
Last Modified: 2014-05-22
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
Comment
Watch Question
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE