TOP Clause functionality within Groups
-
Hi,
Ineed to come up with a TOP 100 by groupings result set. I do not want the TOP 100 records, but I want the "TOP" 100 records for each group in my data.
Data:
Table1 - Code, ID, Paid Amount
Table2 - Name
I have a query that groups and sums the paid amount and then orders by paid amount DESC
Example:
SELECT c.code, p.id, p.name, SUM(c.paid)
FROM table1 AS c
INNER JOIN table2 AS p ON c.id = p.id
WHERE c.code > ' '
GROUP BY c.code,p.id.p.name
HAVING SUM(p.paid) > 0
ORDER BY c.code, SUM(c.paid) DESC
The result set comes out fine (highest paid to smallest paid), but am getting more than 100 records for each c.code.
Example:
Code Count of records
023 116
033 263
045 193
046 128
I should only have 400 records (in this case 100 records for each code since there were that many records).
If a code has only 45 records, then 45 records should come out for that code. For each code 100 or less "TOP" records should come out.
I thought about doing something like adding a derived field
in above query to add a consecutive number (like 1,2,3,etc) to each record for each code group. Hoever, I don't know how to do that. Then I could use criteria to give me records that have 100 or less in this field.
I appreciate any help on this.
Thanks
-
Here's an exemple you can play with (this is for top 90%):
SELECT O.XType
--, count(*) AS TotalHits_Found
, O.name
, (SELECT CEILING(COUNT(*) * 0.9) FROM dbo.SysObjects O4 WHERE O4.XType = O.XType) as [90%]
, (SELECT COUNT(*) FROM dbo.SysObjects O5 WHERE O5.XType = O.XType) AS [100%]
FROM dbo.SysObjects O
WHERE EXISTS (SELECT * FROM dbo.SysObjects O2 WHERE O2.XType = O.XType AND O2.id = O.id and O2.id IN (SELECT TOP 90 PERCENT id FROM dbo.SysObjects O3 WHERE O3.XType = O.XType ORDER BY O3.id))
--GROUP BY O.XType
ORDER BY O.XType
, O.Name
-
-- Temptable
SELECT c.code, p.id, p.name, SUM(c.paid) TotalPaid
into #T1
FROM table1 AS c
INNER JOIN table2 AS p ON c.id = p.id
WHERE c.code > ' '
GROUP BY c.code,p.id.p.name
HAVING SUM(p.paid) > 0
-- Use it
select code, id, name, TotalPaid
from
#T1 d1
where id in (select top 100 id from #T1 d2 where d2.code = d1.code order by totalpaid desc )
order by code,TotalPaid desc
you could create a view and use the above query with the view...
Can't wait for SQL 2005 rowids partitions!!
* Noel -
Thanks for the help.
I had thought of doing it this way, but was trying for something in 1 query instead of multiple.
I thought if there was a way to put a consecutive number in each row 1 - n for for each code, I could then query for <rowid> <= 100 for each code. Identity wouldn't work because I wasn't going to use an SELECT INTO query, plus it would distinguish between codes.
I haven't looked at SQL2005 yet so am not familiar with rowids partitions.
I appreciate the rapid response.
-
I had thought of doing it this way, but was trying for something in 1 query instead of multiple.
Like I said Create a view with your original code then
the "second" query will invoke your view and it will be just one query to the Client side!
* Noel -
It could also be built with a derived table, I think -- if you REALLY want it in one query 🙂
-
Did you guys actually try my query??????
-
Remi, the issue is not your query he needs to aggregate the data before your query is implemented. No matter which method you use the aggregation has to happen first and then you will be able to pick the top participants per group! ( using the count method or the Top clause whichever bring it the fastest )
* Noel -
Sorry, slight misunderstanding... did you get my PM?
-
not really, let me check
* Noel -
Did you get it this time? Looks like you're not getting the notifications of PM anymore (or you're just busy working
. -
I did but it takes time
be patient!
* Noel -
Wouldn't you be a little impatient if you were in my shoes
... and I don't wanna keep him waiting too long.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply