February 19, 2004 at 4:36 am
Hi,
I wonder if I could get your advice.
I've been working on reporting data and I have been asked to retrieve the TOP 10 records per person for a given set of data.
I think this sort of thing may have been raised before so please forgive me for repeating the issue.
Here is my code
declare @part int
declare part_data cursor
for SELECT DISTINCT StaffIndex
FROM tblEngagement INNER JOIN tblstaff ON ClientPartner = staffindex
where staffindex > 0 AND Stafforganisation = 1
open part_data
fetch next from part_data into @part
while (@@fetch_status = 0)
BEGIN
SELECT TOP 10 staffname,clientcode, clientname,sum(DebtAmount) AS DebtTotal
FROM tbl_Billsfortheyear INNER JOIN tblStaff ON staffindex = Partner
WHERE Partner = @Part
group by clientcode, clientname,staffname
order by sum(DebtAmount) DESC
fetch next from part_data into @part
END
CLOSE part_data
deallocate part_data
I have had to first filter out a group of people then pass each persons index into a procedure to find the TOP 10 for that person.
As you can see I am using a cursor, but I wondered if this could be done more efficiently (avoiding cursors).
Many Thanks
Graeme
February 19, 2004 at 5:39 am
I usually use sub-queries:
select t10.Partner, t10.staffname, t10.clientcode, t10.clientname, t10.DebtTotal
from
(SELECT TOP 10 Partner, staffname,clientcode, clientname,sum(DebtAmount) AS DebtTotal
FROM tbl_Billsfortheyear INNER JOIN tblStaff ON staffindex = Partner
group by Partner, clientcode, clientname,staffname
order by sum(DebtAmount) DESC) t10 inner join
(
SELECT DISTINCT StaffIndex
FROM tblEngagement INNER JOIN tblstaff ON ClientPartner = staffindex
where staffindex > 0 AND Stafforganisation = 1
) lst
on t10.Partner = lst.StaffIndex
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
February 19, 2004 at 9:38 am
Russel thanks for that.
That still only returns 10 records.
My routine returns 10 records per staffindex. So if my select distinct staffindex statement returns 10 rows, I should end up with 100 records.
Any thoughts.
Graeme
February 19, 2004 at 10:48 am
Change This on Russel's query:
select t10.Partner, t10.staffname, t10.clientcode, t10.clientname, t10.DebtTotal
from
(SELECT TOP 10 Partner, staffname,clientcode, clientname,sum(DebtAmount) AS DebtTotal
FROM tbl_Billsfortheyear INNER JOIN tblStaff ON staffindex = Partner
group by Partner, clientcode, clientname,staffname
order by sum(DebtAmount) DESC) t10 Right outer join
(
SELECT DISTINCT StaffIndex
FROM tblEngagement INNER JOIN tblstaff ON ClientPartner = staffindex
where staffindex > 0 AND Stafforganisation = 1
) lst
on t10.Partner = lst.StaffIndex
* Noel
February 20, 2004 at 6:47 pm
I don't think that's going to work, as the derived table will still only return 10 rows. You can't use correlated subqueries either, as they must return only one value.
In this case I'd probably be forced to use a while loop:
declare @counter int
set @counter = 0
While 1 = 1
Begin
Select top 1 @counter = counter
From LookupTable
Where counter > @Counter
Order by counter
IF @@Rowcount = 0 Break
Select top 10
From BaseTable
Where counter = @Counter
END
Signature is NULL
February 22, 2004 at 4:49 am
what about making a table with ID and result .... and in your stroed procedure: 1- delete all rows from table 2- insert All values into that table 3- select from table by percentage or no of rows .. (this is instead of Cursors)
Alamir Mohamed
Alamir_mohamed@yahoo.com
February 23, 2004 at 1:24 pm
Without knowing what columns belong to which tables, I cannot confidently write your query, but perhaps this will help you understand how to do this:
USE Northwind
GO
SELECT r.CustomerID, r.OrderID, SUM(e.UnitPrice * e.Quantity) OrderTotal
FROM Orders r JOIN [Order Details] e ON r.OrderID = e.OrderID
WHERE r.OrderID IN
(SELECT TOP 3 d.OrderID
FROM [Order Details] d JOIN Orders o ON d.OrderID = o.OrderID
WHERE o.CustomerID = r.CustomerID
GROUP BY d.OrderID
ORDER BY SUM(d.UnitPrice * d.Quantity) DESC)
GROUP BY r.CustomerID, r.OrderID
ORDER BY r.CustomerID, OrderTotal DESC
--Jonathan
February 26, 2004 at 1:38 am
Many thanks for your input guys.
Calvin you are right it still only returns 10 rows.
Jonathan, that is the sort of thing I am after, I will try to apply that to my problem.
Take it easy and I hope I can be of help to you guys...
Graeme
February 26, 2004 at 4:31 am
Jonathan,
That worked a treat..many thanks. I neat solution. Although the cursor solution runs a lot faster.
Still, it's not run too often.
Thanks again
Graeme
February 26, 2004 at 12:29 pm
"worked a treat" ... ?? Is that Australian? I love slang...
Signature is NULL
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply