How Can I obtain the Top 3 of a dataset - Page 2

Brett Kaiser · #16 (**permalink**) 02-10-05, 11:24

Done...it's been a rough week...they've got me doing project plans...aaaahhhhhhhhh

dolfandave · #17 (**permalink**) 02-11-05, 11:03

"The problem with any set-based solution to this kind of problem is that it does not deal well with "ties" in the data... They make your results a bit "funky", but I don't know any reliable way to resolve that using set based logic."

That is the catching point. I have 20 people, for each of whom I wish to get the "TOP 3" records. When I have a "tie" it fails, giving me only 2 of the 3 desired records.

I inserted a couple of extra records to the table given above by:

INSERT INTO myTable99(SiteId, EmpId, Sales)
SELECT 2, 2, 15.00
SELECT 2, 2, 1500.00

The 1500.00 will create a tie with another record, already having a value of 1500.00.

Here are all the records:

select *
from myTable99

Results:
1 1 10.0000
2 1 15.0000
3 1 20.0000
4 1 50.0000
5 1 10.0000
6 1 5.0000
1 2 100.0000
2 2 1500.0000
3 2 2000.0000
4 2 5000.0000
5 2 1000.0000
6 2 500.0000
1 3 1.0000
2 3 1.5000
3 3 2.0000
4 3 5.0000
5 3 1.0000
6 3 .5000
2 2 1500.0000
2 2 15.0000

When I run your query:

select a.EmpId
, a.SiteId
, a.sales
from myTable99 a
inner
join myTable99 b
on a.EmpId = b.EmpId
and a.Sales <= b.Sales
group
by a.EmpId
, a.SiteId
, a.sales
having count(*) <= 3
order
by a.EmpId
, a.sales desc

My results are here:

1 4 50.0000
1 3 20.0000
1 2 15.0000
2 4 5000.0000
2 3 2000.0000
3 4 5.0000
3 3 2.0000
3 2 1.5000

I need Employee 2 to show at least one of his two 1500.00 records. Any way to do this?

ddave

blindman · #18 (**permalink**) 02-11-05, 11:18

This can still be done with a set-based solution:

Code:

select	a.EmpId,
	a.SiteId,
	a.sales
from	myTable99 a
	inner join myTable99 b
		on a.EmpId = b.EmpId
		and (a.Sales < b.Sales
			or a.Sales = b.Sales and a.EmpID < b.EmpID)
group by a.EmpId,
	a.SiteId,
	a.sales
having count(*) <= 3
order by a.EmpId,
	a.sales desc

Output:

Code:

EmpId       SiteId      sales                 
----------- ----------- --------------------- 
1           3           20.0000
1           2           15.0000
1           1           10.0000
1           5           10.0000
2           3           2000.0000
2           2           1500.0000
2           5           1000.0000
3           3           2.0000
3           2           1.5000
3           1           1.0000
3           5           1.0000

Brett Kaiser · #19 (**permalink**) 02-11-05, 11:22

No kidding....

Code:

    SELECT  a.EmpId, a.SiteId, a.sales
      FROM (SELECT DISTINCT EmpId, SiteId, sales FROM myTable99) a
INNER JOIN (SELECT DISTINCT EmpId, SiteId, sales FROM myTable99) b
        ON a.EmpId  = b.EmpId
       AND a.Sales <= b.Sales
  GROUP BY a.EmpId, a.SiteId, a.sales
  HAVING COUNT(*) <= @N
ORDER BY a.EmpId, a.sales desc
GO

r937 · #20 (**permalink**) 02-11-05, 11:27

without getting into the actual sql, the problem of ties isn't really a problem of sql, it's more of a problem of semantics

blindman's last example is perfect --

3 3 2.0000
3 2 1.5000
3 1 1.0000
3 5 1.0000

as far as i'm concerned, a tie across the last place requires that all rows with that value be included

let's say we had 25 people in a classroom, and we wanted the top 3 students based on marks

joe has a gpa of 3.90, mary has a gpa of 3.85, and all twenty-three others have exactly the same gpa, 3.80

who ya gonna exclude?

Internet Services

Web Development

Digital Marketing

Consumer Tech