Link to home
Start Free TrialLog in
Avatar of Whah
Whah

asked on

Greater than as well as Equal to

MSSQL 2008

For rows with the same "LastName", I am trying to add up all the "Count#" fields and return the ID and LastName of the row with the highest value.

Table:
ID
Count1
Count2
Count3
Count4
LastName

Exmple:
001
23
43
55
2
Smith

006
84
22
0
11
Smith

222
1
0
23
43
Jones

231
11
2
90
2
Jones


Expected return; ID 001 was the biggest of the "Smith" values and ID 231 was the biggest of the "Jones" values:
001|Smith
231|Jones

In a separate query, I am also trying to generate a list of ID|LastName where the counts are equal.  For example:

001
23
43
55
2
Smith

006
84
22
6
11
Smith

Expected return:
001|Smith
006|Smith
Avatar of knightEknight
knightEknight
Flag of United States of America image

>> Expected return; ID 001 was the biggest of the "Smith" values

How so?  The biggest count field for 001 is 55, but the biggest one for 006 is 84.

Please clarify.
You could use two queries
select @maxcount=max(count1+count2+count3+count4)
select Id,lastname from table where (count1+count2+count3+count4)>=@maxcount
This might require the use of a stored procedure that will get the max count from the database table.
Avatar of Whah
Whah

ASKER

@knightEknight - It's the sum of all the Count# fields.
SELECT ID, LastName
FROM (
    SELECT
        ID, LastName,
        ROW_NUMBER() OVER(PARTITION BY LastName ORDER BY count1 + count2 + count3 + count4 DESC) AS row_num
    FROM dbo.tablename
) AS derived
WHERE
    row_num = 1
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial