Home » SQL & PL/SQL » SQL & PL/SQL » Reg : Rank Command
Reg : Rank Command [message #190611] Thu, 31 August 2006 07:00 Go to next message
vivek_g_r
Messages: 15
Registered: May 2005
Junior Member
Cool

Hi,

The following is my query :

Quote:


SELECT DEPT_NAME,SUPL_NAME,SUM(STCU_QTY_SAL) SALE_QTY,SUM(STCU_SAL_VALUE) SALE_VALUE,
RANK() OVER(PARTITION BY DEPT_NAME ORDER BY SUM(STCU_SAL_VALUE)) "RANK"
FROM PSERVER.STOKCUMM,PSERVER.PS_MASTERS
WHERE YEAR_MON=200605 AND STCU_ITEM_CODE=ITEM_CODE
GROUP BY DEPT_NAME,SUPL_NAME
/



I want to Limit the Rank output to 10.

i.e is 1 to 10

Please help me out.

Regards

R.Vivekanandan
Re: Reg : Rank Command [message #190616 is a reply to message #190611] Thu, 31 August 2006 07:06 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

check this, if it works for you.
SELECT * FROM 
     (
SELECT DEPT_NAME,SUPL_NAME,SUM(STCU_QTY_SAL) SALE_QTY,SUM(STCU_SAL_VALUE) SALE_VALUE,
RANK() OVER(PARTITION BY DEPT_NAME ORDER BY SUM(STCU_SAL_VALUE)) RNK
FROM PSERVER.STOKCUMM,PSERVER.PS_MASTERS
WHERE YEAR_MON=200605 AND STCU_ITEM_CODE=ITEM_CODE
GROUP BY DEPT_NAME,SUPL_NAME
    )
WHERE RNK <=10



regards,
Previous Topic: Complex results
Next Topic: QUERY
Goto Forum:
  


Current Time: Sat Dec 03 16:25:04 CST 2016

Total time taken to generate the page: 0.10608 seconds