You are currently viewing our boards as a guest which gives you limited access to view most discussions, articles and access our other FREE features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload your own photos and access many other special features. Registration is fast, simple and absolutely free so please, join our community today!
If you have any problems with the registration process or your account login, please contact contact support.
If you would prefer not to see any double-underlined words and corresponding advertisements, please click here.
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.
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
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.
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
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
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.
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
Linear Mode
