CODEWITHSUNDEEP
sql-server-2012
THOMAS
THOMAS

Reputation: 3

Multiple Top 1 based on count

I have a table with multiple customers and multiple transaction dates.

Cust_ID Trans_Date
------- ----------
C01 2012-02-18
C01 2012-02-27
C01 2012-03-09
C02 2012-02-15
C02 2012-03-09
C03 2012-03-30
C01 2013-01-14
C02 2013-02-21
C03 2013-01-15
C03 2013-03-07

I want to find customers with most transaction in each year and the transactions for that customer. Below is the result I am expecting. 

Year    Cust_ID    nTrans
----    -------    ------
2012    C01        3
2013    C03        2

Can anybody help with the script? SQL Svr version 2012.

Thanking you in advance, Thomas

Upvotes: 0

Views: 41

Answers (3)

Livin Mathew
Livin Mathew

Reputation: 48

You can use the max() function on the column to find the largest value in the column. In this case you can apply max(nTrans)

eg: 

SELECT MAX(column_name) FROM table_name;

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32170

This is the "greatest N per group" problem. It's usually solved with row_number().

;WITH CTE AS (
    SELECT YEAR(Trans_Date) Year,
        Cust_ID,
        COUNT(*) as nTrans,
        ROW_NUMBER() OVER (PARTITION BY YEAR(Trans_Date) ORDER BY COUNT(*) DESC) rn
    FROM Table
    GROUP BY YEAR(Trans_Date),
        Cust_ID
)
SELECT Year,
    Cust_ID,
    nTrans
FROM CTE
WHERE rn = 1
ORDER BY Trans_Year

Strictly speaking, the ROW_NUMBER() here isn't ordered in a deterministic way. As written, if there's a tie in the count, the query just returns one Cust_ID, but there's no guarantee which ID will be returned. It should either be ORDER BY COUNT(*) DESC, Cust_ID to make the results consistent, or you should use RANK() or DENSE_RANK() to allow for ties.

Upvotes: 1

Glenn Vandamme
Glenn Vandamme

Reputation: 1206

I haven't had the chance to test it but your solution should look something like this:

SELECT YEAR(Trans_Date) AS Year, Cust_ID, COUNT(*) AS nTrans
FROM Transactions 
GROUP BY Year, Cust_ID
HAVING MAX(nTrans);

Have a look at Group by functions in SQL.

Upvotes: 0

Related Questions