Обсуждение: HELP, can't implement e filter

Поиск
Список
Период
Сортировка

HELP, can't implement e filter

От
giuseppe.derossi@email.it
Дата:
Hi,
I need to implement a filter in order to select from the first table the
second one...
it could be simple, but i'm wasting time. Please, is there anyone could help
me?
thanks in advance
Giu

  c1     c2 c3     c4     c5
1)133659;1;"0039";"00121";7
2)133664;1;"0039";"00121";12
3)133664;2;"0039";"00121";12
4)133665;2;"0039";"00121";12
5)135460;1;"0039";"01152";
6)135460;2;"0039";"01152";
7)135471;1;"0050";"00153";4
8)135471;2;"0050";"00153";4


1)133659;1;"0039";"00121";7
3)133664;2;"0039";"00121";12
4)133665;2;"0039";"00121";12
6)135460;2;"0039";"01152";
8)135471;2;"0050";"00153";4
 --
 Email.it, the professional e-mail, gratis per te: http://www.email.it/f

 Sponsor:
 Una settimana da sogno nelle più belle località di vacanza, con
Mondolastminute trovi ogni settimana l'offerta che fa per te!
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6852&d=20070819



Re: HELP, can't implement e filter

От
Ben Kim
Дата:
On Sun, 19 Aug 2007, giuseppe.derossi@email.it wrote:

> Hi,
> I need to implement a filter in order to select from the first table the
> second one...
>  c1     c2 c3     c4     c5
> 1)133659;1;"0039";"00121";7
> 2)133664;1;"0039";"00121";12
> 3)133664;2;"0039";"00121";12
> 4)133665;2;"0039";"00121";12
> 5)135460;1;"0039";"01152";
> 6)135460;2;"0039";"01152";
> 7)135471;1;"0050";"00153";4
> 8)135471;2;"0050";"00153";4
>
> 1)133659;1;"0039";"00121";7
> 3)133664;2;"0039";"00121";12
> 4)133665;2;"0039";"00121";12
> 6)135460;2;"0039";"01152";
> 8)135471;2;"0050";"00153";4

There should be something better but this might work.

select * from testtable where (c1,c2) in (select c1, max(c2) from
testtable group by c1 );


HTH

Ben K.
Developer
http://benix.tamu.edu

Re: HELP, can't implement e filter

От
"Scott Marlowe"
Дата:
On 8/19/07, giuseppe.derossi@email.it <giuseppe.derossi@email.it> wrote:
> Hi,
> I need to implement a filter in order to select from the first table the
> second one...
> it could be simple, but i'm wasting time. Please, is there anyone could help
> me?
> thanks in advance
> Giu
>
>   c1     c2 c3     c4     c5
> 1)133659;1;"0039";"00121";7
> 2)133664;1;"0039";"00121";12
> 3)133664;2;"0039";"00121";12
> 4)133665;2;"0039";"00121";12
> 5)135460;1;"0039";"01152";
> 6)135460;2;"0039";"01152";
> 7)135471;1;"0050";"00153";4
> 8)135471;2;"0050";"00153";4
>
>
> 1)133659;1;"0039";"00121";7
> 3)133664;2;"0039";"00121";12
> 4)133665;2;"0039";"00121";12
> 6)135460;2;"0039";"01152";
> 8)135471;2;"0050";"00153";4

Do you want the things in table1 that are in table2, or the things in
table2 that are NOT in table2?

Things in table1 that are in table2:

select * from table1 join table2 on (table1.field1=table2.field1 and
table1.field2=table2.field2 and ....)

things in table1 that are not in table2:

select * from table1 left join table2 on (table1.field1=table2.field2
and table1.field2=table2.field2 and ...) where table1.field1 IS NULL
or table1.field2 IS NULL or ...

replace elipses with the rest of the fields.

Re: HELP, can't implement e filter

От
"David Sturtevant"
Дата:
If I understand you right I think what you want is

select c1,c2,c3,c4,c5 from table1 intersect select c1,c2,c3,c4,c5 from table2

On 19/08/07, giuseppe.derossi@email.it <giuseppe.derossi@email.it> wrote:
Hi,
I need to implement a filter in order to select from the first table the
second one...
it could be simple, but i'm wasting time. Please, is there anyone could help
me?
thanks in advance
Giu

  c1     c2 c3     c4     c5
1)133659;1;"0039";"00121";7
2)133664;1;"0039";"00121";12
3)133664;2;"0039";"00121";12
4)133665;2;"0039";"00121";12
5)135460;1;"0039";"01152";
6)135460;2;"0039";"01152";
7)135471;1;"0050";"00153";4
8)135471;2;"0050";"00153";4


1)133659;1;"0039";"00121";7
3)133664;2;"0039";"00121";12
4)133665;2;"0039";"00121";12
6)135460;2;"0039";"01152";
8)135471;2;"0050";"00153";4
--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f

Sponsor:
Una settimana da sogno nelle più belle località di vacanza, con
Mondolastminute trovi ogni settimana l'offerta che fa per te!
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6852&d=20070819



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

Re: HELP, can't implement e filter

От
giuseppe.derossi@email.it
Дата:
Hi guys,
thanks to everybody for your help. Sorry if I was not too goot at explaining
my problem, well, I have only the first table and I want to implement a
filter which returns the second one. The rule to implement is:
select only one c1 which has the max c2 and min c4, then display only c3,c4.
and c5 in ascendig way.
This is my solution, but I think I can improve it by using th Ben'suggestion
:


select c1,c2,c3,min(c4),min(c5)
-- see min c5 works on not significat values
from first_table
into tab_temp
group by c1,c2,c3
order by c1



select c1,max(c2),c3,c4,c5
into second_table
from tab_temp
group by c1,c3,c4,c5
order by c1,c4,c5

select c3,c4,c5 from second_table order by c3,c4,c5



    --------- Original Message --------
    Da: Ben Kim <bkim@tamu.edu>
    To:
            Cc: pgsql-admin@postgresql.org
    Oggetto: Re: [ADMIN] HELP, can't implement e filter
    Data: 20/08/07 16:15

    >
>
>
> On Sun, 19 Aug 2007, giuseppe.derossi@email.it wrote:
>
> > Hi,
> > I need to implement a filter in order to select from the first table the
> > second one...
> >  c1     c2 c3     c4     c5
> > 1)133659;1;"0039";"00121";7
> > 2)133664;1;"0039";"00121";12
> > 3)133664;2;"0039";"00121";12
> > 4)133665;2;"0039";"00121";12
> > 5)135460;1;"0039";"01152";
> > 6)135460;2;"0039";"01152";
> > 7)135471;1;"0050";"00153";4
> > 8)135471;2;"0050";"00153";4
> >
> > 1)133659;1;"0039";"00121";7
> > 3)133664;2;"0039";"00121";12
> > 4)133665;2;"0039";"00121";12
> > 6)135460;2;"0039";"01152";
> > 8)135471;2;"0050";"00153";4
>
> There should be something better but this might work.
>
> select * from testtable where (c1,c2) in (select c1, max(c2) from
> testtable group by c1 );
>
>
> HTH
>
> Ben K.
> Developer
> http://benix.tamu.edu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>
>
 --
 Email.it, the professional e-mail, gratis per te: http://www.email.it/f

 Sponsor:
 Collezioni Moda Giro d’Italia Fashion. Approfitta dei saldi estivi. Sconti
dal 30 al 50%. Uomo, Donna e Bambino. Prodotto ufficiale.
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6908&d=20070820