How Can I obtain the Top 3 of a dataset

dolfandave · #1 (**permalink**) 02-09-05, 11:19

If I have say 20 records of sales employees, for example, how can I get the top 3 locations for $$$sales for EACH employee? Each employee can have multiple locations where they have sold(let's say up to 50). I only want the names of the top 3 locations. The closest I can get is filtering the dataset by a HAVING clause > a dollar amount but this still gives me between 3 - 12 records for each plus I have to literally enter each salesperson's number as it stands now. Is this a loop or a cursor? Thanks.

ddave

HardCode · #2 (**permalink**) 02-09-05, 11:30

Once you have your SQL set up to return the sales dollars for all of the locations, just add TOP 3 before the select:

Code:

 SELECT TOP 3 Sum(Dollars) FROM MyTable;

Note that you can also SELECT TOP n PERCENT.

blindman · #3 (**permalink**) 02-09-05, 12:04

General solution to your problem:

The TOP N clause in TSQL is useful, but is limited by the fact that it only applies to the entire recordset, and that it will not accept a variable as a parameter.

This method will return any requested number of records for each group of one or more column values in a dataset. It uses a type of join called a Theta join, where the values in two datasets are compared, but do not necessarily have to be equal:

Code:

 
declare @N int
set     @N = 5  --The number of records to return for each grouping.
select  YourTable.YourColumns
from    YourTable
        inner join YourTable ThetaTable
        on YourTable.GroupColumns = ThetaTable.GroupColumns
        and YourTable.SortColumn <= ThetaTable.SortColumn
group by YourTable.YourColumns
having count(*) <= @N

dolfandave · #4 (**permalink**) 02-09-05, 12:11

The TOP function does not work as blindman indicates as it gives me only the top 3 of 120+ records, not the top 3 for EACH person. I will try the Theta join. Thanks to both for replying.

ddave

r937 · #5 (**permalink**) 02-09-05, 14:10

blindman, your queries are starting to look good

but the join should be outdented

use foo and bar if more than one column is intended (YourColumns alone does not show comma syntax)

and shorter aliases make it easier to read

Code:

select  T.foo, T.bar
from    YourTable as T
  inner join YourTable as Th
        on T.GroupColumns = Th.GroupColumns
        and T.SortColumn <= Th.SortColumn
group by T.foo, T.bar
having count(*) <= n

Brett Kaiser · #6 (**permalink**) 02-09-05, 15:41

Quote:

Originally Posted by r937

blindman, your queries are starting to look good

Looks can be deceiving...it doesn't work

Code:


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(SiteId int, EmpId int, Sales money)
GO

INSERT INTO myTable99(SiteId, EmpId, Sales)
SELECT 1, 1, 10.00 UNION ALL
SELECT 2, 1, 15.00 UNION ALL
SELECT 3, 1, 20.00 UNION ALL
SELECT 4, 1, 50.00 UNION ALL
SELECT 5, 1, 10.00 UNION ALL
SELECT 6, 1, 5.00 UNION ALL
SELECT 1, 2, 100.00 UNION ALL
SELECT 2, 2, 1500.00 UNION ALL
SELECT 3, 2, 2000.00 UNION ALL
SELECT 4, 2, 5000.00 UNION ALL
SELECT 5, 2, 1000.00 UNION ALL
SELECT 6, 2, 500.00 UNION ALL
SELECT 1, 3, 1.00 UNION ALL
SELECT 2, 3, 1.50 UNION ALL
SELECT 3, 3, 2.00 UNION ALL
SELECT 4, 3, 5.00 UNION ALL
SELECT 5, 3, 1.00 UNION ALL
SELECT 6, 3, .50
GO

 DECLARE @N int
     SET @N = 3  --The number of records to return for each grouping.

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

SET NOCOUNT OFF
DROP TABLE myTable99
GO

I know I've seen this work somehow though

Pat Phelan · #7 (**permalink**) 02-09-05, 17:49

At least to me, it is a lot more intuitively obvious to do this via a sub-query, something like:

Code:

 DECLARE @N int
     SET @N = 3  --The number of records to return for each grouping.

SELECT a.SiteId, a.EmpId, a.Sales
   FROM myTable99 a 
   WHERE (SELECT Count(*)
      FROM myTable99 b
      WHERE  b.EmpId   = a.EmpId
         AND a.Sales  <= b.Sales) <= @N
   ORDER BY a.EmpID, a.Sales DESC, a.SiteID

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.

-PatP

r937 · #8 (**permalink**) 02-09-05, 19:05

Quote:

Originally Posted by Brett Kaiser

I know I've seen this work somehow though

yeah, it's tricky

here's what you were searching for --

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
  group 
      by a.EmpId
       , a.SiteId
       , a.sales
  having count(*) <= 3
  order 
      by a.EmpId
       , a.sales desc

which produces the following (correct) results:

Code:

1	4	50.0000
1	3	20.0000
1	2	15.0000

2	4	5000.0000
2	3	2000.0000
2	2	1500.0000

3	4	5.0000
3	3	2.0000
3	2	1.5000

pat, i like the subquery method too, i guess i just got used to the join solution after so many times showing pre-4.1 mysql people how to do it

blindman · #9 (**permalink**) 02-09-05, 21:44

I indents 'em as I wants 'em, thank you!

Brett Kaiser · #10 (**permalink**) 02-10-05, 09:37

Hey thanks guys...

And yes Pat, I agree it may be more intuitive...but have a look at the plans...it looks like Rudy's Join is more effecient...even with the group by.

I would not have guessed this. Anyone care if I blog this? With appropriate references of course.

It's just one of things I know can be done...and I forget how to contruct it.

Anyway, here's the plans

r937 · #11 (**permalink**) 02-10-05, 09:43

blog away

Brett Kaiser · #12 (**permalink**) 02-10-05, 10:04

OK, You've all been Blogged

r937 · #13 (**permalink**) 02-10-05, 10:24

Quote:

Originally Posted by Brett Kaiser

OK, You've all been Blogged

ta very much

and usually i just ignore your frequent typos, but in this case, i must insist that you correct the spelling of my surname

thanks in advance

Brett Kaiser · #14 (**permalink**) 02-10-05, 10:55

mia culpa...done

r937 · #15 (**permalink**) 02-10-05, 11:03

Quote:

Originally Posted by Brett Kaiser

mia culpa...done

thanks

and now, would you mind changing the link so that it points to the correct person