asked on

SQL Syntax Help Group By type query

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

Jason Schlueter

I don't have access to check this at the moment but does this put you on the right track?

DECLARE @temp TABLE
  (
     loanid INT,
     amount INT
  )

INSERT INTO @temp
SELECT loanid,
       amount
FROM   loans
WHERE  Isnull(relseq, 0) = 1
UNION
SELECT loanid,
       Max(amount)
FROM   loans
WHERE  relseq IS NULL
GROUP  BY relid

SELECT loanid
FROM   @temp

Open in new window

Scott Pletcher

SELECT LoadId
FROM (
SELECT
LoadId,
ROW_NUMBER() OVER (PARTITION BY RelID ORDER BY RelSeq DESC, Amount DESC) AS row_num
FROM dbo.tablename
) AS subquery1
WHERE
row_num = 1

sbornstein2

ASKER

Scott pretty close but this is taking also the records that don't have a grouped count > 1 for RelID but on there own. For example:

Example Set of data:

LoanId      PoolId      RelSeq      RelID      Amount
7030      1123      NULL      1123-1      4468547.79
7031      1123      NULL      1123-2      2724929.25
7032      1123      NULL      1123-3      2675334.52
7033      1123      NULL      1123-1      2665100.77
7034      1123      NULL      1123-5      2277303.22
7035      1123      NULL      1123-6      2185009.11
7036      1123      NULL      1123-7      1020178.69
7037      1123      NULL      1123-1      859755.98
7038      1123      NULL      1123-9      849044.17
7039      1123      NULL      1123-10      676773.52
7040      1123      NULL      1123-11      512084.90
7041      1123      NULL      1123-12      487753.29
7042      1123      NULL      1123-13      456048.65
7043      1123      NULL      1123-14      442754.03
7044      1123      NULL      1123-15      442350.84

That returns 13 Rows where in my case in my detail would be only:
LoanID
7030

as it's the only with RelID grouped count > 1 and out of the 3 records with RelID 1123-1 it has the highest amount. Also I the RelSeq Desc may be an issue later as I need to go after where it is = 1 in the case where there is a. Such as if I had:
7045      1123      NULL      1123-99      456048.65
7046      1123      1      1123-99      442754.03
7047      1123      NULL      1123-99      442350.84

I would want the 7046 record.

Perfect World
I will increase the points of this to 500 if I could get help with this which I think would make my life a lot easier. If I could return a record set like this would be perfect in my case not sure how hard this would be to do but a huge help for me:

If I had this data here:
LoanId      PoolId      RelSeq      RelID      Amount
7030      1123      NULL      1123-1      4468547.79
7031      1123      NULL      1123-2      2724929.25
7032      1123      NULL      1123-3      2675334.52
7033      1123      NULL      1123-1      2665100.77
7034      1123      NULL      1123-5      2277303.22
7035      1123      NULL      1123-6      2185009.11
7036      1123      NULL      1123-7      1020178.69
7037      1123      NULL      1123-1      859755.98
7038      1123      NULL      1123-9      849044.17
7039      1123      NULL      1123-10      676773.52
7040      1123      NULL      1123-11      512084.90
7041      1123      NULL      1123-12      487753.29
7042      1123      NULL      1123-13      456048.65
7043      1123      NULL      1123-14      442754.03
7044      1123      NULL      1123-15      442350.84
7045      1123      NULL      1123-99      456048.65
7046      1123      1      1123-99      442754.03
7047      1123      NULL      1123-99      442350.84

If I could get this return it would be awesome and save me a lot of queries.

LoanId      PoolId      RelSeq      RelID      Amount IsPrimary SecondaryLoanId
7030      1123      NULL      1123-1      4468547.79 1 NULL
7031      1123      NULL      1123-2      2724929.25 NULL NULL
7032      1123      NULL      1123-3      2675334.52 NULL NULL
7033      1123      NULL      1123-1      2665100.77 NULL 7030
7034      1123      NULL      1123-5      2277303.22 NULL NULL
7035      1123      NULL      1123-6      2185009.11 NULL NULL
7036      1123      NULL      1123-7      1020178.69 NULL NULL
7037      1123      NULL      1123-1      859755.98 NULL 7030
7038      1123      NULL      1123-9      849044.17 NULL NULL
7039      1123      NULL      1123-10      676773.52 NULL NULL
7040      1123      NULL      1123-11      512084.90 NULL NULL
7041      1123      NULL      1123-12      487753.29 NULL NULL
7042      1123      NULL      1123-13      456048.65 NULL NULL
7043      1123      NULL      1123-14      442754.03 NULL NULL
7044      1123      NULL      1123-15      442350.84 NULL NULL
7045      1123      NULL      1123-99      456048.65 NULL 7046
7046      1123      1      1123-99      442754.03 1 NULL
7047      1123      NULL      1123-99      442350.84 NULL 7046

This would make my life so much easier to go after the way I need to put some report data together. My idea is to be able to query to get the Primary Loans only and then the secondary loans for that primary then all the NULL NULL loans I need next. Any help would be much appreciated.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sbornstein2

ASKER

thanks