CODEWITHSUNDEEP
sql-serversql-server-2008
Pramod Lawate
Pramod Lawate

Reputation: 1045

How to get max from PARTITION BY Clause using CTE

WITH ABC AS 
    (
        SELECT ACCOUNTID,ROW_NUMBER() OVER (PARTITION BY ACCOUNTID ORDER BY ACCOUNTID) AS NUMBER FROM BankRequest
    )

    SELECT * FROM ABC

Current Query give me output like below

    ACCOUNTID   NUMBER
    897         1
    897         2
    897         3
    1110        1
    1110        2
    1110        3
    1110        4
    1119        1
    1119        2
    1119        3
    1119        4
    1146        1
    1146        2
    1182        1
    1395        1
    1395        2
    1395        3
    1395        4
    1395        5

Expected output would be like

    ACCOUNTID   NUMBER
    897         3
    1110        4
    1119        4
    1146        2
    1182        1
    1395        5

I am using SQL 2016 and trying achieve get multiple record from BankRequest table. In this table each accountid having multiple record as i mentioned above first table. but i want only last transaction id for each accountid from BankRequest table.

Please help me for this problem or any better alternative way where i can achieve expected output.

Upvotes: 0

Views: 948

Answers (4)

Aman Prajapati
Aman Prajapati

Reputation: 157

You can use CTE + Row_Number() + Max() like below one

;WITH BankRequest_Row_Number_By_ACCOUNTID
AS
(
    SELECT  ACCOUNTID, 
            Row_NUmber() over (Partition by ACCOUNTID order by ACCOUNTID) AS rowNumber_ACCOUNTID
    FROM    BankRequest

)
SELECT  ACCOUNTID,
        MAX(rowNumber_ACCOUNTID) AS NUMBER
FROM    BankRequest_Row_Number_By_ACCOUNTID
GROUP BY ACCOUNTID

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

Use ORDER BY AccountID DESC, and select where the row number is 1.

However, I'm thinking that you gave us an incomplete data set.

"i want only last transaction id for each accountid" means you should probably really ORDER BY transactionId DESC

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

Or you can use your same cte and get Number = 1 as below:

;WITH ABC AS 
(
    SELECT ACCOUNTID,NUMBER, ROW_NUMBER() OVER (PARTITION BY ACCOUNTID ORDER BY Number DESC) AS RowN FROM BankRequest
)
SELECT ACCOUNTID, NUMBER FROM ABC
where RowN = 1

This is similar to top 1 with ties which John mentioned

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

Two quick options in the final select

Select AccountID,Number=max(number) 
 From  ABC 
 Group By AccountID

Or

Select Top 1 with ties *
 From  ABC
 Order By Row_Number() over (Partition By AccountID Order By Number Desc)

Or eliminate the CTE 

Select AccountId
       Number = count(*)
 From  BankRequest
 Group By AccountId

Upvotes: 1

Related Questions