top values for each unique employee in table
-
I'm not sure what you call this, so I had no luck searching for info on the topic. I've got the following table of employees, covering multiple Employers:
EmployerID int,
EmployeeID int,
EmployeeInfo varchar(10)
This table contains multiple records per EmployeeID as well as multiple EmployerID values. What I want is the two most frequent EmployeeInfo values for each EmployeeID. IF an employee only has one distinct EmployeeInfo value, one record is all that is needed. Also having the count of each would be nice, but not essential. For example, if the table contained the following rows:
789, 111, value one
789, 111, value one
789, 111, value two
789, 111, value two
789, 111, value two
789, 111, value xyz
555, 222, value x
555, 222, value x
555, 222, value x
555, 222, value m
555, 222, value m
555, 222, value t
555, 222, value k
I would want the following result set:
EmployerID EmployeeID EmployeeInfo Occurences --------------------------------------------- 789 111 value two 3 789 111 value one 2 555 222 value x 3 555 222 value m 2
Many thanks for anyone who can offer some code or even suggestions on how to do this. I imagine I could do it with a cursor and #temp table but I'm hoping there is a more efficient way to do it in a single query?
- Eric
-
select EmployerID,EmployeeID,EmployeeInfo, Count(*) as TCnt
from [YourTable]
group by EmployerID,EmployeeID,EmployeeInfo
Having Count(*) > 1
order by EmployeeID,TCnt desc
* Noel -
I appreciate the suggestion, noeld, but that actually returns all records which have more than one EmployeeInfo value for the same Employee. With my sample data posted, it looks correct, but as you add more data to the table, you get more than the two highest counts per employee.
I was able to code it yesterday using a cursor to loop over the distinct EmployeeID values and selecting their two most frequent records and inserting them into a #temp table. I just thought there must be a way to do this in a single query, without the cursor and multiple steps.
Thanks again,
- Eric
-
See the two methods below. Method #1 will have terrible performance if your employees table has many rows. Method #2 performs the count(*) once into a temp table. Use method #2 for large # of rows.
create table Employees
(
EmployerID int,
EmployeeID int,
EmployeeInfo varchar(10)
)
insert into Employees values (789, 111, 'value one')
insert into Employees values (789, 111, 'value one')
insert into Employees values (789, 111, 'value two')
insert into Employees values (789, 111, 'value two')
insert into Employees values (789, 111, 'value two')
insert into Employees values (789, 111, 'value xyz')
insert into Employees values (555, 222, 'value x')
insert into Employees values (555, 222, 'value x')
insert into Employees values (555, 222, 'value x')
insert into Employees values (555, 222, 'value m')
insert into Employees values (555, 222, 'value m')
insert into Employees values (555, 222, 'value t')
insert into Employees values (555, 222, 'value k')
--
-- Method #1
--
select
EmployerID,
EmployeeID,
EmployeeInfo,
count(*) as Occurrence
from
Employees E1
group by
EmployerID,
EmployeeID,
EmployeeInfo
having
count(*) in
(
select top 2
count(*)
from
Employees E2
where
E1.EmployeeID = E2.EmployeeID
group by
EmployeeID,
EmployeeInfo
order by
EmployeeID,
count(*) desc
)
--
-- Method #2
--
select
EmployerID,
EmployeeID,
EmployeeInfo,
count(*) as Occurrence
into
#TopValues
from
Employees
group by
EmployerID,
EmployeeID,
EmployeeInfo
select
TV.EmployerID,
TV.EmployeeID,
TV.EmployeeInfo,
TV.Occurrence
from
#TopValues TV
where
TV.Occurrence in
(
select top 2
Occurrence
from
#TopValues TV1
where
TV1.EmployeeID = TV.EmployeeID
order by
Occurrence desc
)
order by
TV.EmployeeID,
TV.Occurrence desc
drop table #TopValues
Good Luck,
srv
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply