Home » SQL & PL/SQL » SQL & PL/SQL » Get record by length (10g)
Get record by length [message #400197] Mon, 27 April 2009 02:10 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Is there any easy way to achieve the following without making use of analytic funtion.

create table longer_length(
   id varchar2(10),
   col1 varchar2(10),
   col2 varchar2(10),
   col3 varchar2(10))
   

insert into longer_length values(1,'aaa','bbb','ccc');
insert into longer_length values(1,'aaaaaa','bbbbbbb','cccccc');
insert into longer_length values(1,'aaaa','bbbb','cccc');
insert into longer_length values(2,'aaaaaa','bbbbbbb','cccccc');
insert into longer_length values(2,'aaa','bbb','ccc');



The following will give me the length of each record for the field im interested in

select id,col1,col2,col3,length(col1||col2||col3) from longer_length


Is there any way i could select the record having the max length group by id?
Re: Get record by length [message #400201 is a reply to message #400197] Mon, 27 April 2009 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to use analytical functions when they are made for?

Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: Get record by length [message #400207 is a reply to message #400201] Mon, 27 April 2009 02:54 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
row_number() is quite interesting. The thing is that analytical function works slow at times. But it does the job. Im going for the sql below, thank you michel,


SELECT id,col1,col2,col3 from
    (select id,col1,col2,col3,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY length(col1||col2||col3) desc) AS gf 
            FROM longer_length) 
     where gf =1;

[Updated on: Mon, 27 April 2009 03:45] by Moderator

Report message to a moderator

Re: Get record by length [message #400314 is a reply to message #400197] Mon, 27 April 2009 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> But it does the job.
This better than not doing the job!
Re: Get record by length [message #400317 is a reply to message #400314] Mon, 27 April 2009 09:07 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Yes, you are absolutely right, you guys have contributed to a lot of my queries, thanks a million for all this and god bless!
Previous Topic: Query giving ORA-01652 Error
Next Topic: Query to Copy data from one table to nother
Goto Forum:
  


Current Time: Sat Dec 10 12:37:23 CST 2016

Total time taken to generate the page: 0.08683 seconds