Postgres Pro
Downloads
Home   >   mailing lists

Thread: combining records from a single table and presenting them as one record

combining records from a single table and presenting them as one record

From
Abhishek
Date:
Hi Everyone
 
I have a table "TABLE1"  which has
----------------------------------------------------
Callguid      |       digits     |      type
----------------------------------------------------
xxxx                   123               'a'
xxxx                   345               'b'
xxxx                   678               'c'
 
type can have only 'a', 'b' or 'c' as its value.
I am tryng to write a query which returns me a record like this
 
-------------------------------------------------------------------------------------------------------
CallGuid      |       a type digits       |     b type digits     |     c type digits
-------------------------------------------------------------------------------------------------------
xxxx                    123                         345                       678
 
 
I do the query as this:
 
select callguid , ( select digits from TABEL1 where type='a' ), ( select digits from TABEL1 where type='b' ), ( select digits from TABEL1 where type='c' ) from TABLE1; 
 
result:
-------------------------------------------------------------------------------------------------------
CallGuid      |       a type digits       |     b type digits     |     c type digits
-------------------------------------------------------------------------------------------------------
xxxx                    123                         345                       678
xxxx                    123                         345                       678
xxxx                    123                         345                       678
 
Note that I get the records which i wanted, but I get them duplicated. If I use 'distinct' keyword I get the desired result with no duplication. But 'distinct' seems to be too expensive.
 
Is there any better way I can do this without compromising the performance ?
 
Your suggestions and input are very appreciated
 
Thanks
AJ
 


--
Abhishek Jain
 

Re: combining records from a single table and presenting them as one record

From
boinger
Date:
On 10/27/05, Abhishek <ajcool@gmail.com> wrote:
> I am tryng to write a query which returns me a record like this
<snip>
> I do the query as this:
>
> select callguid , ( select digits from TABEL1 where type='a' ), ( select
> digits from TABEL1 where type='b' ), ( select digits from TABEL1 where
> type='c' ) from TABLE1;
<snip>
> Note that I get the records which i wanted, but I get them duplicated. If I
> use 'distinct' keyword I get the desired result with no duplication. But
> 'distinct' seems to be too expensive.
>
> Is there any better way I can do this without compromising the performance ?

Just add 'LIMIT 1' to the end of the query.

Re: combining records from a single table and presenting them as one record

From
Abhishek
Date:
Using LIMIT 1 does return me a single record but please note that the table can have multiple guids for which I need a unique record with all the digit types combined. Using LIMIT 1 returns me always one record. :-(

On 10/27/05, boinger <boinger@gmail.com> wrote:
On 10/27/05, Abhishek <ajcool@gmail.com> wrote:
> I am tryng to write a query which returns me a record like this
<snip>
> I do the query as this:
>
> select callguid , ( select digits from TABEL1 where type='a' ), ( select
> digits from TABEL1 where type='b' ), ( select digits from TABEL1 where
> type='c' ) from TABLE1;
<snip>
> Note that I get the records which i wanted, but I get them duplicated. If I
> use 'distinct' keyword I get the desired result with no duplication. But
> 'distinct' seems to be too expensive.
>
> Is there any better way I can do this without compromising the performance ?

Just add 'LIMIT 1' to the end of the query.



--
Abhishek Jain

Re: combining records from a single table and presenting them as one record

From
Michael Fuhr
Date:
On Fri, Oct 28, 2005 at 11:47:12AM -0400, Abhishek wrote:
> Using LIMIT 1 does return me a single record but please note that the table
> can have multiple guids for which I need a unique record with all the digit
> types combined. Using LIMIT 1 returns me always one record. :-(

Have you looked at contrib/tablefunc?

-- 
Michael Fuhr