pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcombining records from a single table and presenting them as one record
5+ messages / 4 participants
[nested] [flat]
* combining records from a single table and presenting them as one record
@ 2005-10-27 18:00 Abhishek <ajcool@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Abhishek @ 2005-10-27 18:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: combining records from a single table and presenting them as one record
@ 2005-10-27 21:46 boinger <boinger@gmail.com>
parent: Abhishek <ajcool@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: boinger @ 2005-10-27 21:46 UTC (permalink / raw)
To: Abhishek <ajcool@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: combining records from a single table and presenting
@ 2005-10-27 23:37 Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Frank Bax @ 2005-10-27 23:37 UTC (permalink / raw)
To: pgsql-sql
At 02:00 PM 10/27/05, Abhishek wrote:
>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
1) You didn't say if 'a','b','c' records always exist for every callguid.
2) You didn't say if there is more than one record for a given callguid/type.
If (1) is 'yes' and (2) is 'no'
select a.callguid, a.digits as a_digits, b.digits as b_digits, c.digits as
c_digits
from
(select callguid,digits from table1 where type='a') as a
join
(select callguid,digits from table1 where type='b') as b on
a.callguid=b.callguid
join
(select callguid,digits from table1 where type='c') as c on
a.callguid=c.callguid;
If (1) is 'no' and (2) is 'no'
select coalesce(a.callguid,b.callguid,c.callguid) as callguid,
a.digits as a_digits, b.digits as b_digits, c.digits as c_digits
from
(select callguid,digits from table1 where type='a') as a
full outer join
(select callguid,digits from table1 where type='b') as b on
a.callguid=b.callguid
full outer join
(select callguid,digits from table1 where type='c') as c on
a.callguid=c.callguid;
If (2) is 'yes', you're on your own. You can also try searching for
"crosstab" and/or "pivot table" for more info.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: combining records from a single table and presenting them as one record
@ 2005-10-28 15:47 Abhishek <ajcool@gmail.com>
parent: boinger <boinger@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Abhishek @ 2005-10-28 15:47 UTC (permalink / raw)
To: boinger <boinger@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: combining records from a single table and presenting them as one record
@ 2005-10-28 17:12 Michael Fuhr <mike@fuhr.org>
parent: Abhishek <ajcool@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-10-28 17:12 UTC (permalink / raw)
To: Abhishek <ajcool@gmail.com>; +Cc: boinger <boinger@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-28 17:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-27 18:00 combining records from a single table and presenting them as one record Abhishek <ajcool@gmail.com>
2005-10-27 21:46 ` boinger <boinger@gmail.com>
2005-10-28 15:47 ` Abhishek <ajcool@gmail.com>
2005-10-28 17:12 ` Michael Fuhr <mike@fuhr.org>
2005-10-27 23:37 Re: combining records from a single table and presenting Frank Bax <fbax@sympatico.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox