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

[Abhishek]

Hi Everyone
 
I have a table "TABLE1"  which has

Callguid  |   digits |  type

   123   'a'
   345   'b'
   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
---
    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
---
    123 345   678
    123 345   678
    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: [SQL] combining records from a single table and presenting them as one record

[Abhishek]

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 <[EMAIL PROTECTED]> wrote:
On 10/27/05, Abhishek <[EMAIL PROTECTED]> wrote:> I am tryng to write a query which returns me a record like this
> 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;
> 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: [SQL] Using EXPLAIN-ANALYZE

[Abhishek]

http://www.postgresql.org/docs/7.2/static/performance-tips.html
 
EXPLAIN is explaied quite nicely with examples here. Hope that helps
 
Bests
AJ 
On 2/16/06, Kashmira Patel (kupatel) <[EMAIL PROTECTED]> wrote:

Hi all,
   I am pretty new to using Postrgres, and have been looking at the messages in this forum for a while. I have noticed that the use of EXPLAIN-ANALYZE is recommended quite a lot. I read the Postgres docs, but am not quite sure I understand how this works. Is there some tutorial or any other documentation how this can be used?

 
Thanks a lot,
Kashmira Patel-- Bests,AJ