Home » SQL & PL/SQL » SQL & PL/SQL » How to limit the ranking
How to limit the ranking [message #252659] Thu, 19 July 2007 10:22 Go to next message
palohotox
Messages: 7
Registered: July 2007
Location: Malaysia
Junior Member

How do we apply rank or dense_rank with rownumber limitation..

Say after applying ranking to set of data

Select item,code,dense_rank() over(order by item||code) as rank,count(*) over (partition by item||code)
from inv

..I got

item,code,rank,count
abc,12,1,5
abc,12,1,5
abc,12,1,5
abc,12,1,5
abc,12,1,5
efg,12,2,3
efg,12,2,3
efg,12,2,3

say for rank=1 I got 5 rows in it..How do I re-rank to limit only max 3 lines in each rank so that I got the output such as..

abc,12,1,3
abc,12,1,3
abc,12,1,3
abc,12,2,2
abc,12,2,2
efg,12,3,3
efg,12,3,3
efg,12,3,3

Thanks
Re: How to limit the ranking [message #252670 is a reply to message #252659] Thu, 19 July 2007 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then,
with inv as (
  select item, code,
         trunc(row_number() over (partition by item, code order by rowid)/3) grp
  from inv
  )
select item, code,
       dense_rank () over (order by item, code, grp) as rank,
       count(*) over (partition by item, code, grp) as count,
from inv
/

Regards
Michel
Re: How to limit the ranking [message #252697 is a reply to message #252670] Thu, 19 July 2007 12:44 Go to previous messageGo to next message
palohotox
Messages: 7
Registered: July 2007
Location: Malaysia
Junior Member

Thanks for the reply..

But incase I'm using a group by statement.. Oracle will gave error message.. 'Cannot use rowid in view having distinct,group by etc..

Any other way..?
Re: How to limit the ranking [message #252700 is a reply to message #252697] Thu, 19 July 2007 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the query execution with the error.

Regards
Michel
Re: How to limit the ranking [message #252701 is a reply to message #252700] Thu, 19 July 2007 13:12 Go to previous messageGo to next message
palohotox
Messages: 7
Registered: July 2007
Location: Malaysia
Junior Member


WITH inv AS (
SELECT
ITEMID,DESCRIPTION,LOC,STARTDATE,SCHEDDATE,--NEEDDATE,
QTY,FIRMPLANSW,PURCHMETHOD,COI,COD,ITEMCATEGORY,VENDORCODE,VENDORNAME,
ACTIONALLOWEDSW,ORDERER,VENDGROUP,DELIVERYTIME,ITEMBUYERGROUPID,
SHIPMODE,PURPOSE,AVPACKAGEPROFILE,
SHIPMODE,PURPOSE,AVPACKAGEPROFILE,TRUNC( row_number() over (PARTITION BY VENDORCODE , SHIPMODE, PURPOSE, ITEMBUYERGROUPID ORDER BY ROWID)/26 ) grp
FROM
(
SELECT
ITEMID,DESCRIPTION,LOC,STARTDATE,SCHEDDATE,--NEEDDATE,
SUM(QTY) QTY,FIRMPLANSW,PURCHMETHOD,COI,COD,ITEMCATEGORY,VENDORCODE,VENDORNAME,
ACTIONALLOWEDSW,ORDERER,VENDGROUP,DELIVERYTIME,ITEMBUYERGROUPID,SHIPMODE,PURPOSE,AVPACKAGEPROFILE
FROM
(SELECT A.*,
B.VENDGROUP
,D.DELIVERYTIME,C.ITEMBUYERGROUPID,
DECODE(C.AVSHIPCODE,CHR(2),DECODE(B.CURRENCY,'MYR','ROAD',''),C.AVSHIPCODE) SHIPMODE ,
--DECODE(B.CURRENCY,'MYR','ROAD',DECODE(C.AVSHIPCODE,CHR(2),' ' ,C.AVSHIPCODE)) SHIPMODE
C.DIMENSION3_ PURPOSE,C.AVPACKAGEPROFILE
FROM FIRMPPURCHFRMANU A,
VENDTABLE B ,INVENTTABLE C,INVENTTABLEMODULE D
WHERE B.dataareaid='835'
AND C.dataareaid='835'
AND D.dataareaid='835'
AND A.VENDORCODE=trim(B.accountnum)
AND C.ITEMID=A.ITEMID
AND A.ITEMID=D.ITEMID
AND D.MODULETYPE=1
AND A.VENDORCODE <> 'I1845'
)) GROUP BY ITEMID,DESCRIPTION,LOC,STARTDATE,SCHEDDATE,
--NEEDDATE,
--QTY,
FIRMPLANSW,PURCHMETHOD,COI,COD,ITEMCATEGORY,VENDORCODE,VENDORNAME,
ACTIONALLOWEDSW,ORDERER,VENDGROUP,DELIVERYTIME,ITEMBUYERGROUPID,
SHIPMODE,PURPOSE,AVPACKAGEPROFILE
))
SELECT
ITEMID,DESCRIPTION,LOC,STARTDATE,SCHEDDATE,--NEEDDATE,
SUM(QTY)QTY,FIRMPLANSW,PURCHMETHOD,COI,COD,ITEMCATEGORY,VENDORCODE,VENDORNAME,
ACTIONALLOWEDSW,ORDERER,VENDGROUP,DELIVERYTIME,ITEMBUYERGROUPID,
SHIPMODE,PURPOSE,AVPACKAGEPROFILE,
RANK() OVER (ORDER BY VENDORCODE || SHIPMODE|| PURPOSE|| ITEMBUYERGROUPID) seq,
dense_RANK() OVER (ORDER BY VENDORCODE || SHIPMODE|| PURPOSE|| ITEMBUYERGROUPID) seq,
COUNT (*) over (PARTITION BY VENDORCODE || SHIPMODE|| PURPOSE|| ITEMBUYERGROUPID ORDER BY VENDORCODE) cnt,
DECODE(SIGN((COUNT (*) over (PARTITION BY VENDORCODE || SHIPMODE|| PURPOSE|| ITEMBUYERGROUPID ORDER BY VENDORCODE) ) -26),-1,0,0,0,1,(COUNT (*) over (PARTITION BY VENDORCODE || SHIPMODE|| PURPOSE|| ITEMBUYERGROUPID ORDER BY VENDORCODE) ) /26 ) ac--,
-- COUNT (*) over (PARTITION BY VENDORCODE || SHIPMODE|| PURPOSE|| ITEMBUYERGROUPID ORDER BY STARTDATE RANGE 26 PRECEDING ) asd
--TRUNC(row_number() over (PARTITION BY VENDORCODE || SHIPMODE|| PURPOSE|| ITEMBUYERGROUPID ORDER BY ROWID)/26 ) grp
FROM
INV


Re: How to limit the ranking [message #252703 is a reply to message #252701] Thu, 19 July 2007 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Format
2/ Simplify your query to fit your example

Regards
Michel
Re: How to limit the ranking [message #252710 is a reply to message #252703] Thu, 19 July 2007 13:48 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Thu, 19 July 2007 13:20
1/ Format
2/ Simplify your query to fit your example

Regards
Michel



3/ Cut and Paste the error you are getting
Re: How to limit the ranking [message #252711 is a reply to message #252703] Thu, 19 July 2007 13:55 Go to previous messageGo to next message
palohotox
Messages: 7
Registered: July 2007
Location: Malaysia
Junior Member

Here's the simplified one..

SELECT
ITEMID,STARTDATE,SCHEDDATE,
SUM(QTY),ITEMCATEGORY,VENDORCODE,VENDORNAME,
ITEMBUYERGROUPID,
TRUNC( row_number() over (PARTITION BY VENDORCODE , SHIPMODE, PURPOSE, ITEMBUYERGROUPID ORDER BY ROWID)/26 ) grp
FROM
(SELECT A.*,
B.VENDGROUP
,D.DELIVERYTIME,C.ITEMBUYERGROUPID,B.CURRENCY,
DECODE(C.AVSHIPCODE,CHR(2),DECODE(B.CURRENCY,'MYR','ROAD',''),C.AVSHIPCODE) SHIPMODE ,
C.DIMENSION3_ PURPOSE,C.AVPACKAGEPROFILE,ROWNUM
FROM FIRMPPURCHFRMANU A,
VENDTABLE B ,INVENTTABLE C,INVENTTABLEMODULE D
WHERE B.dataareaid='835'
AND C.dataareaid='835'
AND D.dataareaid='835'
AND A.VENDORCODE=trim(B.accountnum)
AND C.ITEMID=A.ITEMID
AND A.ITEMID=D.ITEMID
AND D.MODULETYPE=1
AND A.VENDORCODE <> 'I1845'
) GROUP BY ITEMID,STARTDATE,SCHEDDATE,
ITEMBUYERGROUPID,
SHIPMODE,PURPOSE,AVPACKAGEPROFILE,VENDORCODE
Re: How to limit the ranking [message #252713 is a reply to message #252711] Thu, 19 July 2007 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Follow the first link of my first post.

Regards
Michel
Re: How to limit the ranking [message #252997 is a reply to message #252659] Sat, 21 July 2007 05:43 Go to previous message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
SQL> with inv as (select 'abc' item, 12 code from dual union all
  2             select 'abc' item, 12 code from dual union all
  3             select 'abc' item, 12 code from dual union all
  4             select 'abc' item, 12 code from dual union all
  5             select 'abc' item, 12 code from dual union all
  6             select 'efg' item, 12 code from dual union all
  7             select 'efg' item, 12 code from dual union all
  8             select 'efg' item, 12 code from dual),
  9       t as (Select item,
 10         code,
 11         trunc((row_number() over (partition by item,code order by 1)-1)/3) rn
 12    from inv)
 13    --
 14    select item,
 15           code,
 16           dense_rank() over(order by item,code, rn) as rank,
 17           count(*) over(partition by item,code, rn) count
 18      from t
 19  /

ITEM       CODE       RANK      COUNT
---- ---------- ---------- ----------
abc          12          1          3
abc          12          1          3
abc          12          1          3
abc          12          2          2
abc          12          2          2
efg          12          3          3
efg          12          3          3
efg          12          3          3

8 rows selected

SQL> 
Previous Topic: returning ref cursor in procedures
Next Topic: only answer my question if you have nothing better to do
Goto Forum:
  


Current Time: Sun Dec 04 00:28:11 CST 2016

Total time taken to generate the page: 0.11401 seconds