Finding max value of second order variable SQL

Question

I have a table in my database in the following form:

Name   Value  Count
Angus   B   70
Angus   C   2054
Angus   D   418
Betty   B   16
Betty   C   2
Betty   T   37
Betty   E   683
Colin   K   85
Colin   L   297
Colin   M   810

I need to be able to pick the highest count per name and retain the values - I would do this with an order by but the 'Count' column is a count of other unique values within the table so can't do a GROUP By on Count.

Any tips greatly appreciated

To add further complication, Name and Value are on different databases which I have joined — Jack Smith, Nov 08 '17 at 14:25

score 0 · Answer 1 · answered Nov 08 '17 at 14:23

You can try the following query:

SELECT
    Name, Value, Count
FROM
(
    SELECT Name, Value, Count,
        RANK() OVER (PARTITION BY Name ORDER BY Count DESC) rank
    FROM yourTable
) t
WHERE t.rank = 1;

This would yield multiple records for a given name should 2 or more records happen to have the same exact count. If you really need just one record, then you should reveal your logic for breaking the tie. In that case, we might be able to just use ROW_NUMBER instead of a rank analytic function.

score 0 · Answer 2 · answered Nov 08 '17 at 14:24

0

Row_number will do this

with cte as
(
select Name 
       ,Value
       ,Count
       , ROW_NUMBER () over (partition by name order by [Count] desc) rn
       ) select name,value,[Count] from Cte where rn = 1

answered Nov 08 '17 at 14:24

Ven

2,011
1
13
27

Beware of names which are tied with the same count. – Tim Biegeleisen Nov 08 '17 at 14:27

Finding max value of second order variable SQL

2 Answers2