Finding the max value and the corresponding row. 1

[asimeqi]

I have a table like this:

DECLARE @operator_activity TABLE (
	account_id 	INT,	
	operator_id 	INT,
	num_trans 	INT
)

I need to find the max value of num_trans for each account and also the corresponding operator_id.
So I write something like this:

DECLARE @operator_activity TABLE (
	account_id 	INT,	
	operator_id 	INT,
	num_trans 	INT
)

INSERT INTO @operator_activity VALUES (1, 1, 1)
INSERT INTO @operator_activity VALUES (1, 2, 1)
INSERT INTO @operator_activity VALUES (1, 3, 2)
INSERT INTO @operator_activity VALUES (2, 2, 1)
INSERT INTO @operator_activity VALUES (2, 4, 1)



SELECT oa.account_id,
	max_num_trans,
	operator_id
FROM @operator_activity oa
JOIN (
	SELECT account_id,
		MAX(num_trans) AS max_num_trans
	FROM @operator_activity
	GROUP BY account_id) AS A
ON oa.account_id = A.account_id
WHERE num_trans = max_num_trans

I get these results:

account_id  max_num_trans operator_id 
----------- ------------- ----------- 
2           1             2
2           1             4
1           2             3

For account_id = 1 I get the result that I wanted.
For account_id = 2 I get 2 rows.
This result makes sense, since there are 2 operators that have num_trans = max_num_trans. However this is not what I want. I want for every account to get only one row. If there are 2 or more rows that have the max value only one would be selected somehow (I do not care which one).
So in the above case I would like to get:

account_id  max_num_trans operator_id 
----------- ------------- ----------- 
2           1             2
1           2             3

or:

account_id  max_num_trans operator_id 
----------- ------------- ----------- 
2           1             4
1           2             3

Ideas?

 

[gmmastros]

SELECT oa.account_id,
    max_num_trans,
    [!]Min([/!]operator_id[!]) As Operator_Id[/!]
FROM @operator_activity oa
JOIN (
    SELECT account_id,
        MAX(num_trans) AS max_num_trans
    FROM @operator_activity
    GROUP BY account_id) AS A
ON oa.account_id = A.account_id
And oa.num_trans = A.max_num_trans
[!]Group By oa.account_id, A.max_num_trans[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

DECLARE @operator_activity TABLE (
    account_id     INT,    
    operator_id     INT,
    num_trans     INT
)

INSERT INTO @operator_activity VALUES (1, 1, 1)
INSERT INTO @operator_activity VALUES (1, 2, 1)
INSERT INTO @operator_activity VALUES (1, 3, 2)
INSERT INTO @operator_activity VALUES (2, 2, 1)
INSERT INTO @operator_activity VALUES (2, 4, 1)

SELECT oa.account_id,
       oa.num_trans,
       oa.operator_id
FROM @operator_activity oa
JOIN (
    SELECT account_id,
        MAX(num_trans) AS max_num_trans
    FROM @operator_activity
    GROUP BY account_id) AS A
ON oa.account_id = A.account_id AND oA.num_trans = a.max_num_trans

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SQLDenis]

or this one

DECLARE @operator_activity TABLE (
    account_id     INT,    
    operator_id     INT,
    num_trans     INT
)

INSERT INTO @operator_activity VALUES (1, 1, 1)
INSERT INTO @operator_activity VALUES (1, 2, 1)
INSERT INTO @operator_activity VALUES (1, 3, 2)
INSERT INTO @operator_activity VALUES (2, 2, 1)
INSERT INTO @operator_activity VALUES (2, 4, 1)


SELECT oa.account_id,
    max_num_trans,
    operator_id

FROM @operator_activity oa
JOIN (
    SELECT account_id,
        MAX(num_trans) AS max_num_trans,
	MAX(operator_id) AS max_operator
    FROM @operator_activity
    GROUP BY account_id) AS A
ON oa.account_id = A.account_id
AND oa.num_trans = a.max_num_trans
AND a.max_operator =oa.operator_id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Denis,

Add this data.

INSERT INTO @operator_activity VALUES (2, 2, 10)

Since the MaxOperatorID does not correspond to the max number of transactions......

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

yep, true

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[asimeqi]

Thank you guys. That was very helpful.