Re: [GENERAL] limit left join rows to 1

[Jure Ložar]

Andreas Kretschmer wrote:


Jure Ložar <[EMAIL PROTECTED]> schrieb:

 


Hi.

Is it possible to limit number of left join rows that match condition to 1? 
I don't want to have hits from first table multiplied when more then 1 row 
matches on left join condition.
   



I'm not sure if i understand you correctly, but perhaps this is what you
are searching for:

Suppose, you have 2 tables, master and detail:

test=# select * from master;
id

 1
 2
(2 rows)

test=# select * from detail;
id | val
+-
 1 | 200
 2 | 200
 1 | 100
(3 rows)


This is the left join:

test=# select m.id, d.val from master m left join detail d on m.id=d.id;
id | val
+-
 1 | 100
 1 | 200
 2 | 200
(3 rows)


But you need only one row from detail, which? Suppose, this one with the
max(val) value:

test=# select m.id, d.val from master m left join (select id, max(val)
as val from detail group by id) d on m.id=d.id;
id | val
+-
 1 | 200
 2 | 200
(2 rows)


Is this okay for you?


Andreas
 


Yes. It's good. Not exactly what I ment but it works.

Thank you.

Jure

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] limit left join rows to 1

[Jure Ložar]

Hi.

Is it possible to limit number of left join rows that match condition to 
1? I don't want to have hits from first table multiplied when more then 
1 row matches on left join condition.


Thank you
Jure

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] How to read wal?

[Jure Ložar]

Hi.

I have a big problem. I dropped wrong table. I do have very recent dump, 
but I would need to read wal (write ahead log) files somehow, to see 
last 60 entries that were made. Then I can enter them manually.
Is there any way to make wal files more readable? Or is there something 
else that I can do? I read that it is not possible to combine them with 
dump. I do not have any usefull logs and wal files are my last hope.


Hopefully I chose the right list po post this.

Thanks in advance

Jure

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly