Home » SQL & PL/SQL » SQL & PL/SQL » selecting all data from a single row (10.2.0.3)
selecting all data from a single row [message #414926] Fri, 24 July 2009 01:16 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Hello All
I have a table that contains millions of records and around 100 fields.Data it contains is as follows.
col1	col2	col3	col4
-----------------------------
abc	123	23	443
abc	23	145     34
cd	45	34      cde

Now i need to select all fieds from a single row and grouped by col1. The result can be either
col1	col2	col3	col4
-----------------------------
abc	123	23	443
cd	45	34      cde

or
col1	col2	col3	col4
-----------------------------
abc	23	145     34
cd	45	34      cde

If i take
select col1,max(col2),max(col3),max(col4) from tbl1
group by col1.
I get the result as
col1	col2	col3	col4
-----------------------------
abc	123	145	443
cd	45	34      cde

Here col2 and col4 are from one row where as col3 is from another row for col1=abc.
But i want all values from same row. I also used analytic function
select col1,
first_value (col2) OVER (PARTITION BY col1 order by 1 asc) ,
first_value (col3) OVER (PARTITION BY col1 order by 1 asc) ,
first_value (col4) OVER (PARTITION BY col1 order by 1 asc) 
from tbl1

But it really took a lot of time. I guess since col1 has far too many values so it had to create many windows.So got slower.
Any idea on how it could be done the fast way.

Thankyou.
--Maheshmhs

Re: selecting all data from a single row [message #414927 is a reply to message #414926] Fri, 24 July 2009 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try with row_number function.
select ...
from ( select t.*, row_number() over(partition by col1 order by null) rn from t )
where rn=1;

Regards
Michel
Re: selecting all data from a single row [message #414961 is a reply to message #414927] Fri, 24 July 2009 03:05 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Thank You Michel.
It Works.
Re: selecting all data from a single row [message #414992 is a reply to message #414926] Fri, 24 July 2009 05:59 Go to previous message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
You could try the aggregrate and analytic functions.

SELECT col1,
MAX(col2) KEEP (dense_rank first ORDER BY null) col2,
MAX(col3) KEEP (dense_rank first ORDER BY null) col3,
MAX(col4) KEEP (dense_rank first ORDER BY null) col4
FROM t
GROUP BY col1;

Previous Topic: number of columns
Next Topic: Function-base index's problem
Goto Forum:
  


Current Time: Tue Dec 06 02:34:10 CST 2016

Total time taken to generate the page: 0.15142 seconds