Home » SQL & PL/SQL » SQL & PL/SQL » Rank in query (Oracle 9i)
Rank in query [message #429067] Sun, 01 November 2009 23:33 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to find the records which are 1 less than the max date.
I have tried using RANK function bu its not working as required.

CREATE TABLE SMP_ANNUAL_PLAN
(
  VENDOR_LOCATION_ITEM_ID  NUMBER               NOT NULL,
  MONTH                    VARCHAR2(20 BYTE),
  YEAR                     NUMBER,
  QUANTITY                 NUMBER               NOT NULL,
  CREATIONDATETIME         DATE                 NOT NULL
)


INSERT INTO SMP_ANNUAL_PLAN ( VENDOR_LOCATION_ITEM_ID, MONTH, YEAR, QUANTITY,
CREATIONDATETIME ) VALUES ( 
39351, 'Nov', 2009, 110,  TO_Date( '10/31/2009 04:52:09 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO SMP_ANNUAL_PLAN ( VENDOR_LOCATION_ITEM_ID, MONTH, YEAR, QUANTITY,
CREATIONDATETIME ) VALUES ( 
39351, 'Dec', 2009, 111,  TO_Date( '10/31/2009 04:52:09 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO SMP_ANNUAL_PLAN ( VENDOR_LOCATION_ITEM_ID, MONTH, YEAR, QUANTITY,
CREATIONDATETIME ) VALUES ( 
39351, 'Jan', 2010, 112,  TO_Date( '10/31/2009 04:52:09 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO SMP_ANNUAL_PLAN ( VENDOR_LOCATION_ITEM_ID, MONTH, YEAR, QUANTITY,
CREATIONDATETIME ) VALUES ( 
39351, 'Nov', 2009, 220,  TO_Date( '11/01/2009 04:52:09 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO SMP_ANNUAL_PLAN ( VENDOR_LOCATION_ITEM_ID, MONTH, YEAR, QUANTITY,
CREATIONDATETIME ) VALUES ( 
39351, 'Dec', 2009, 221,  TO_Date( '11/01/2009 04:52:09 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO SMP_ANNUAL_PLAN ( VENDOR_LOCATION_ITEM_ID, MONTH, YEAR, QUANTITY,
CREATIONDATETIME ) VALUES ( 
39351, 'Jan', 2010, 222,  TO_Date( '11/01/2009 04:52:09 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO SMP_ANNUAL_PLAN ( VENDOR_LOCATION_ITEM_ID, MONTH, YEAR, QUANTITY,
CREATIONDATETIME ) VALUES ( 
39351, 'Jan', 2010, 222,  TO_Date( '11/02/2009 04:52:09 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO SMP_ANNUAL_PLAN ( VENDOR_LOCATION_ITEM_ID, MONTH, YEAR, QUANTITY,
CREATIONDATETIME ) VALUES ( 
39351, 'Jan', 2010, 222,  TO_Date( '11/02/2009 04:52:09 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO SMP_ANNUAL_PLAN ( VENDOR_LOCATION_ITEM_ID, MONTH, YEAR, QUANTITY,
CREATIONDATETIME ) VALUES ( 
39351, 'Jan', 2010, 222,  TO_Date( '11/02/2009 04:52:09 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
COMMIT;

I should get the records only for date '11/2/2009 4:52:09 PM' as this is 1 less than the max date.

select VENDOR_LOCATION_ITEM_ID,MONTH,year,quantity,CREATIONDATETIME,min(rank) from(
select VENDOR_LOCATION_ITEM_ID,MONTH,year,quantity,CREATIONDATETIME, 
       RANK() over (partition by  VENDOR_LOCATION_ITEM_ID 
         order by CREATIONDATETIME desc ) "RANK" 
 from SMP_ANNUAL_PLAN  order by CREATIONDATETIME )
 where rank <>1
 group by VENDOR_LOCATION_ITEM_ID,MONTH,year,quantity,CREATIONDATETIME


I have tried but I am getting all records expect the max date one. Is there a way to get the data which have rank 2 only.

Regards,
Mahi

[Updated on: Sun, 01 November 2009 23:54]

Report message to a moderator

Re: Rank in query [message #429070 is a reply to message #429067] Mon, 02 November 2009 00:10 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Change your query to use DENSE_RANK() is 2.

regards,
Delna
Re: Rank in query [message #429071 is a reply to message #429070] Mon, 02 November 2009 00:17 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks ...it worked Smile

select VENDOR_LOCATION_ITEM_ID,MONTH,year,quantity,CREATIONDATETIME from(
select VENDOR_LOCATION_ITEM_ID,MONTH,year,quantity,CREATIONDATETIME, 
       DENSE_RANK() over (partition by  VENDOR_LOCATION_ITEM_ID 
         order by CREATIONDATETIME desc ) "RANK" 
 from SMP_ANNUAL_PLAN  order by CREATIONDATETIME )
 where RANK  =2
 group by VENDOR_LOCATION_ITEM_ID,MONTH,year,quantity,CREATIONDATETIME



Regards,
Mahi
Re: Rank in query [message #429073 is a reply to message #429071] Mon, 02 November 2009 00:30 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
 group by VENDOR_LOCATION_ITEM_ID,MONTH,year,quantity,CREATIONDATETIME

not required.

regards,
Delna
Previous Topic: same version of DB, different query results.
Next Topic: ORA-00604/ORA-06502 on create package, validates after recompile
Goto Forum:
  


Current Time: Wed Sep 28 04:04:00 CDT 2016

Total time taken to generate the page: 0.13754 seconds