RE: Order by with partition

From: Ken Naim <kennaim_at_gmail.com>
Date: Tue, 9 May 2006 16:43:40 -0500
Message-ID: <00b401c673b1$a3eb72c0$b4ae6a44@KenHome>

If the partition name is stored in the table or can be inferred from the table then yes it can be done.

If you partitioned by month and year for example in a column called hire_date you can do

Select case when (row_number () over partition by (to_char(hire_date,'MMYY') order by hire_date) )=1 then 'partition '|| to_char(hire_date,'Mon YYYY') else null end partition_name, last_name, firstname From ....

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dean Paul
Sent: Tuesday, May 09, 2006 11:47 AM
To: oracle-l_at_freelists.org
Subject: Order by with partition

Hi,

can we use order by or group by on clause on partition? emp(last_name char(10), first_name char(20)); This table has 3 partition like p1,p2,pf. Can I get report like ,

partition       lastname first
partition1   xx                 aaa
                dd                pppp
partition2   ll                  jujuju
                lo                  kkkkk
partitionm ij                   sdjjdfj

Thanks

Don't just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue May 09 2006 - 16:43:40 CDT