Home » SQL & PL/SQL » SQL & PL/SQL » row number count
row number count [message #259425] Wed, 15 August 2007 10:40 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I have a query

SELECT    
  TO_CHAR(RECORD_DATE,'YYYY-MM-DD') AS "NAME",   
  PORTFOLIO_ID  AS "PORTFOLIO_ID", MAX(TOTAL_MV) AS "MARKETVALUE",     
  COUNT(*) AS "NAIC_SHIFTS_CCM"  
FROM  
  NAIC_UP_DOWN_VIEW OUTER  
WHERE  
  (( DECODE('20', '20', 1, 0) = 1 AND ORG_NAIC > CURRENT_NAIC) OR  
  ( DECODE ('20', '21', 1, 0) = 1 AND ORG_NAIC < CURRENT_NAIC)) AND 
  PORTFOLIO_ID = 'BLAC'  
  AND RECORD_DATE=TO_DATE('2002-12-31','YYYY-MM-DD')  
GROUP BY 
  RECORD_DATE, PORTFOLIO_ID, COMP_ID, CLIENT_ID, CCMSECTYPE 
ORDER BY RECORD_DATE,CCMSECTYPE 


Now I wanted to limit the resultset to 1500 records so what I did was

SELECT * FROM(
SELECT 
  ROWNUM RN, 
  TO_CHAR(RECORD_DATE,'YYYY-MM-DD') AS "NAME",   
  PORTFOLIO_ID  AS "PORTFOLIO_ID", MAX(TOTAL_MV) AS "MARKETVALUE",     
  COUNT(*) AS "NAIC_SHIFTS_CCM"  
FROM  
  NAIC_UP_DOWN_VIEW OUTER  
WHERE  
  (( DECODE('20', '20', 1, 0) = 1 AND ORG_NAIC > CURRENT_NAIC) OR  
  ( DECODE ('20', '21', 1, 0) = 1 AND ORG_NAIC < CURRENT_NAIC)) AND 
  PORTFOLIO_ID = 'BLAC'  
  AND RECORD_DATE=TO_DATE('2002-12-31','YYYY-MM-DD')  
GROUP BY 
  RECORD_DATE, PORTFOLIO_ID, COMP_ID, CLIENT_ID, CCMSECTYPE,ROWNUM  
ORDER BY RECORD_DATE,CCMSECTYPE )WHERE  RN BETWEEN 0 and 1500 




Now as you might have observed I had to add ROWNUM RN, to the select statement and group by clause. But doing that changes the resultset because of the group by RN. Is there an alternalte way of doing this?

Some way that I can add rownum to the select clause and not the group by????

[Updated on: Wed, 15 August 2007 10:41]

Report message to a moderator

Re: row number count [message #259428 is a reply to message #259425] Wed, 15 August 2007 10:45 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Move the rownum to the outer query.

SELECT * FROM(
SELECT 
  TO_CHAR(RECORD_DATE,'YYYY-MM-DD') AS "NAME",   
  PORTFOLIO_ID  AS "PORTFOLIO_ID", MAX(TOTAL_MV) AS "MARKETVALUE",     
  COUNT(*) AS "NAIC_SHIFTS_CCM"  
FROM  
  NAIC_UP_DOWN_VIEW OUTER  
WHERE  
  (( DECODE('20', '20', 1, 0) = 1 AND ORG_NAIC > CURRENT_NAIC) OR  
  ( DECODE ('20', '21', 1, 0) = 1 AND ORG_NAIC < CURRENT_NAIC)) AND 
  PORTFOLIO_ID = 'BLAC'  
  AND RECORD_DATE=TO_DATE('2002-12-31','YYYY-MM-DD')  
GROUP BY 
  RECORD_DATE, PORTFOLIO_ID, COMP_ID, CLIENT_ID, CCMSECTYPE
ORDER BY RECORD_DATE,CCMSECTYPE )
WHERE  ROWNUM BETWEEN 0 and 1500 

Previous Topic: Create table as select....
Next Topic: update table problem
Goto Forum:
  


Current Time: Tue Dec 06 11:55:05 CST 2016

Total time taken to generate the page: 0.12816 seconds