Selecting The Highest Seq Number By Nested Joining

I would like to take biggest sequence number for each client Id (biggest sequence number will be calculated based on highest bank account balance). This table has 100000 records. T

Solution 1:

If your able to use row_number function then should work:

select*from 
(
    select 
        t1.ClID, t1.SeqId, t3.Bal, 
        RowNumber =row_number() over (PARTITIONBY t1.ClID orderby t3.bal desc)
    from 
        ClientSeqTable t1
    innerjoin
        SeqBranchTable t2 on t2.SeqId = t1.SeqId
    innerjoin
        Balancetable t3 on t3.BalID = t2.BalID
) t
where
    t.RowNumber =1

The important bit is row number partition by client id and then order by balance descending.

Solution 2:

If you wanted to get your In-line on MAX you could do it this way

SELECT t1.ClID, 
       t1.SeqId, 
       t3.Balance 
FROM   ClientSeqTable t1 
       INNERJOIN SeqBranchTable t2 
         ON t2.SeqId = t1.SeqId 
       INNERJOIN Balancetable t3 
         ON t3.BalID = t2.BalID 
       INNERJOIN (SELECTMax(Balance) Bal, 
                          t1.ClID 
                   FROM   ClientSeqTable t1 
                          INNERJOIN SeqBranchTable t2 
                            ON t2.SeqId = t1.SeqId 
                          INNERJOIN Balancetable t3 
                            ON t3.BalID = t2.BalID 
                   GROUPBY t1.ClID) max_bal 
         ON t1.ClID = max_bal.ClID 
            AND t3.Balance = max_bal.bal  

DEMO

But you should note this is not actually equivalent to using row_number (mouters solution). This may return multiple rows per ClID if there's a tie for max(balance). If you need that way of handling ties and you wanted to use a window function you could use RANK.