Home » SQL & PL/SQL » SQL & PL/SQL » partition by (
partition by [message #409080] Fri, 19 June 2009 04:45 Go to next message
Messages: 93
Registered: October 2008
Location: Nepal
I have a table t1.
  row_num NUMBER(3,0)  NULL,
  col1    VARCHAR2(15) NULL,
  col2    VARCHAR2(15) NULL,
  col3    VARCHAR2(5)  NULL,
  col4    VARCHAR2(5)  NULL

Now i want col2, col3 and col4. It has to be grouped by col2. col3 and col4 could be any values for particular col2. but should be from same row. ie I can't use
select col2,max(col3),max(col4) from t1 group by col2.
As it could give col3 and col4 from different rows. No matter from which rows it selects the values, i want them to be from the same row.
I could do it two ways.
SELECT DISTINCT col3,col2,col4 FROM t1
WHERE col2||col4 IN
SELECT Max(col2||col4) FROM t1

select distinct
first_value (col2) OVER (PARTITION BY COL3)col2,
first_value (col4) OVER (PARTITION BY COL3)col4 

The table above is a sample table. Actual table contains 37 million records and around 20 columns. Is there any other fast and less space(temporary) consuming way.
Re: partition by [message #409081 is a reply to message #409080] Fri, 19 June 2009 05:05 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you try to explain what the problem that you're trying to solve is? If we understood what the higher level problem is, we'd be much more able to give you decent advice.

Your second example contradicts the first, as you seem to be grouping by col3 in the second one.

Personally, I'd do this - a variant on one of your solutions that only hits the table once:
create table test_226 (col_1 varchar2(30), col_2  varchar2(30), col_3 varchar2(30));

insert into test_226 values ('1','2','3');
insert into test_226 values ('1','3','2');
insert into test_226 values ('1','4','1');
insert into test_226 values ('1','1','7');
insert into test_226 values ('2','5','3');
insert into test_226 values ('2','1','4');
insert into test_226 values ('2','2','3');

select col_1
      ,rtrim(substr(max_col,1,30)) col_2
      ,rtrim(substr(max_col,31,30)) col_3
from  (select col_1
             ,max(rpad(col_2,30)||rpad(col_3,30)) max_col
       from   test_226
       group by col_1);
Previous Topic: Left Outer join, eliminate extra records
Next Topic: select - various count
Goto Forum:

Current Time: Fri Apr 28 13:20:32 CDT 2017

Total time taken to generate the page: 0.30544 seconds