Avatar of sbornstein2
sbornstein2
 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
Microsoft SQL Server

Avatar of undefined
Last Comment
sbornstein2

8/22/2022 - Mon
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sbornstein2

ASKER
thanks