| limiting number of rows in a group by function call [message #20125] |
Tue, 30 April 2002 07:50  |
ks
Messages: 12
Registered: January 2002
|
Junior Member |
|
|
what i want to do is, bring back the first 10 rows.
but when i use the rownum clause to limit the number of rows, it does a group by using the first 10 rows and brings back only one row as you can see from the example.
So how do i limit the number of rows to 10? please help! Thanx!
1 select to_char(b.deposittime,'DD-MON-YYYY') as dummiedate,
2 b.carrierkey as ckey,
3 a.payername as pname,
4 c.claimtypedescription as clmtype,
5 count(*) as clmcnt,
6 TO_CHAR(sum(b.totalamount/100),'L999G999G999G999D99MI') as clmamt
7 from tbl_payercarrierref a, tbl_claimshistory b,tbl_claimtyperef c
8 where
9 b.deposittime >= to_timestamp('01-FEB-2002','DD-MON-YYYY')
10 and b.deposittime < to_timestamp('01-FEB-2002','DD-MON-YYYY') + 1
11 and b.carrierkey = a.carrierkey
12 and b.claimtype = c.claimtype
13 and b.billcode = '0'
14 and rownum between 1 and 11
15 group by to_char(b.deposittime,'DD-MON-YYYY'),b.carrierkey,a.payername,c.claimtypedescription
16* order by clmcnt desc
SQL> /
DUMMIEDATE CKEY
----------- ---------
PNAME
--------------------------------------------------------------------------------
CLMTYPE CLMCNT CLMAMT
------------------------------ ---------- -----------------------------
01-FEB-2002 HP_9N000
NEIC(WebMD)
NEIC 11 $4,590.00
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
