Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I get max value based on count? Expand / Collapse
Author
Message
Posted Saturday, April 20, 2013 2:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 11:22 AM
Points: 12, Visits: 21
I have a table that has some result that scan over a period of several days. I want to display a value who's count is the largest over the time range. For example I have the following values in a column.

EmpID Name Value
1 Tim One
1 Tim Two
1 Tim Three
1 Tim One
1 Tim One
1 Tim Two
1 Tim One

Since One shows up the most, I want to display that value. I would like my output to be

EmpId Name Value
1 Tim One

The other scenario is when I have two counts that are the same. For example:

EmpID Name Value
1 Tim One
1 Tim Two
1 Tim Three
1 Tim One
1 Tim Two
1 Tim Two
1 Tim One

In this case, I would want to select the maximum of value which would be Two in this case.
Post #1444741
Posted Saturday, April 20, 2013 2:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 1,886, Visits: 18,561
jkury (4/20/2013)
I have a table that has some result that scan over a period of several days. I want to display a value who's count is the largest over the time range. For example I have the following values in a column.

EmpID Name Value
1 Tim One
1 Tim Two
1 Tim Three
1 Tim One
1 Tim One
1 Tim Two
1 Tim One

Since One shows up the most, I want to display that value. I would like my output to be

EmpId Name Value
1 Tim One

The other scenario is when I have two counts that are the same. For example:

EmpID Name Value
1 Tim One
1 Tim Two
1 Tim Three
1 Tim One
1 Tim Two
1 Tim Two
1 Tim One

In this case, I would want to select the maximum of value which would be Two in this case.


what have you tried so far..? .post the code that is causing your problem.

hint...GROUP BY and COUNT


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1444743
Posted Saturday, April 20, 2013 2:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 11:22 AM
Points: 12, Visits: 21
J Livingston SQL (4/20/2013)
jkury (4/20/2013)
I have a table that has some result that scan over a period of several days. I want to display a value who's count is the largest over the time range. For example I have the following values in a column.

EmpID Name Value
1 Tim One
1 Tim Two
1 Tim Three
1 Tim One
1 Tim One
1 Tim Two
1 Tim One

Since One shows up the most, I want to display that value. I would like my output to be

EmpId Name Value
1 Tim One

The other scenario is when I have two counts that are the same. For example:

EmpID Name Value
1 Tim One
1 Tim Two
1 Tim Three
1 Tim One
1 Tim Two
1 Tim Two
1 Tim One

In this case, I would want to select the maximum of value which would be Two in this case.


what have you tried so far..? .post the code that is causing your problem.

hint...GROUP BY and COUNT


I think I found my solution on another post.
;with cte as (
select empId, name,ROW_NUMBER() over (Partition by empId, name order by count(value) desc) as ranks
from table group by empId, name
)
select empId, name, value from cte where cte.ranks=1


This is the first time I am using CTE.
Post #1444744
Posted Saturday, April 20, 2013 4:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
You store the number as a text representation? Have you tested it with other values? I think you may have just got lucky here?

Yes, Two is "higher" than One ("T" > "O") but what if the 2 highest (equal) records are Two and Three? 3 is higher than 2 but "Tw" is > Th"

Dird



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1444752
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse