| Finding the top 20 values [message #371168] |
Tue, 12 September 2000 06:19  |
Chris
Messages: 128
Registered: November 1998
|
Senior Member |
|
|
Hi Everyone,
I was wondering if someone could tell me how I might retrieve the top 20 values from a query. I have a table that has BILLINGID AND USAGE. First I must go in and SUM all the values in the table grouped by BILLINGID. After that I need to figure out which records are the top 20 largest values or top 20 percent. I know this can't be that difficult but I'd certainly appreciate any input.
Here's my query as it stands:
SELECT I.BILLINGID, ROUND((SUM(I.DELTA)/3600),2) AS USAGE
FROM ACCT_BACKUP I
WHERE I.TYPE = 2
AND TRUNC(I.CREATED) >='08-AUG-00'
AND TRUNC(I.CREATED) <='07-SEP-00'
GROUP BY I.BILLINGID
|
|
|
|
| Re: Finding the top 20 values [message #371200 is a reply to message #371168] |
Thu, 14 September 2000 09:15  |
Vjeko
Messages: 15
Registered: August 2000
|
Junior Member |
|
|
Try this one:
SELECT B1.BILLINGID,B1.USAGE FROM
(SELECT I.BILLINGID billingid, ROUND((SUM(I.DELTA)/3600),2) USAGE
FROM ACCT_BACKUP I
WHERE I.TYPE = 2
AND TRUNC(I.CREATED) >='08-AUG-00'
AND TRUNC(I.CREATED) <='07-SEP-00'
GROUP BY I.BILLINGID
having ROUND((SUM(I.DELTA)/3600),2)>0) B1
WHERE 20>=(SELECT COUNT(*) FROM
(SELECT I.BILLINGID billingid, ROUND((SUM(I.DELTA)/3600),2) USAGE
FROM ACCT_BACKUP I
WHERE I.TYPE = 2
AND TRUNC(I.CREATED) >='08-AUG-00'
AND TRUNC(I.CREATED) <='07-SEP-00'
GROUP BY I.BILLINGID
having ROUND((SUM(I.DELTA)/3600),2)>0) B2
WHERE B2.BILLINGID>=B1.BILLINGID)
ORDER BY B1.USAGE DESC;
This will certainly work. Maybe slow, but correctly.
Good luck!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
