pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL Question
71+ messages / 51 participants
[nested] [flat]

* SQL Question
@ 1999-03-10 05:45  Matthew <matt@ctlno.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Matthew @ 1999-03-10 05:45 UTC (permalink / raw)
  To: pgsql-general

I am using RH5.2 / Postgre 6.3.2  I need a query that has a having
clause.  In 6.3.2 it says that having is not supported yet.   I looked
at the changes in 6.4 and it appears that 6.4.2 supports this.  Is this
true?  Also I have found 6.4.2 rpms but no data rpm so now when I try to
use psql it says pgReadData() -- backend closed the channel
unexpectedly.

Please help.

Matt 



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* RE: [GENERAL] SQL Question
@ 1999-03-10 06:05  Michael Davis <michael.davis@prevuenet.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Michael Davis @ 1999-03-10 06:05 UTC (permalink / raw)
  To: 'Matthew' <matt@ctlno.com>; pgsql-general

Yes, the "having" clause  is supported in 6.4.2 with some restrictions as
outlined in the documentation on the web site.

	-----Original Message-----
	From:	Matthew [SMTP:matt@ctlno.com]
	Sent:	Tuesday, March 09, 1999 10:46 PM
	To:	pgsql-general@postgreSQL.org
	Subject:	[GENERAL] SQL Question

	I am using RH5.2 / Postgre 6.3.2  I need a query that has a having
	clause.  In 6.3.2 it says that having is not supported yet.   I
looked
	at the changes in 6.4 and it appears that 6.4.2 supports this.  Is
this
	true?  Also I have found 6.4.2 rpms but no data rpm so now when I
try to
	use psql it says pgReadData() -- backend closed the channel
	unexpectedly.

	Please help.

	Matt 



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* sql question
@ 1999-11-17 19:53  Herbert Liechti <Herbert.Liechti@thinx.ch>
  0 siblings, 0 replies; 71+ messages in thread

From: Herbert Liechti @ 1999-11-17 19:53 UTC (permalink / raw)
  To: pgsql-general

I have a sql problem which I can't solve. The following table is defined

create table AdressGroup
(
   AdrGroup_Id                     INTEGER       NOT NULL
         DEFAULT NEXTVAL('adrverw_id_seq'),
   Zeit                            DATETIME      NOT NULL,
   Group_Id                        INTEGER       NOT NULL,
   Adress_Id                       INTEGER       NOT NULL
);

The table assigns adresses to adressgroups.

I'd like to select the adresses which belongs to one specific adressGroup and to no other group. If an adress has more than one entry in the AdressGroup
table it should not be in the projection.

I tried the following:

SELECT * FROM adressGroup
 WHERE Group_Id = 6
   AND EXISTS( SELECT AdrGroup_Id FROM adressGroup ag_alias
                WHERE adressGroup.AdrGroup_Id = ag_alias.AdrGroup_Id
                GROUP BY AdrGroup_Id HAVING COUNT(*) == 1 );

When I do this I get the following error:
  ERROR:  rewrite: aggregate column of view must be at rigth side in qual
  ERROR:  rewrite: aggregate column of view must be at rigth side in qual


Does anybody knows the solution? Thanks

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti                            E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services               Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~






^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: [GENERAL] sql question
@ 1999-11-18 12:32  Herbert Liechti <Herbert.Liechti@thinx.ch>
  0 siblings, 0 replies; 71+ messages in thread

From: Herbert Liechti @ 1999-11-18 12:32 UTC (permalink / raw)
  To: pgsql-general

Alain.Tesio@sip.fr wrote:

> > >  SELECT * FROM adressGroup
> > >   WHERE Group_Id = 6
> > >     AND EXISTS( SELECT AdrGroup_Id FROM adressGroup ag_alias
> > >                  WHERE adressGroup.AdrGroup_Id = ag_alias.AdrGroup_Id
> > >                  GROUP BY AdrGroup_Id HAVING COUNT(*) = 1);
>
> One last try : your query looks pretty strange, maybe you should design it again
> from scratch :
> if I understand well, you want the rows in adressGroup with :
>
> - Group_Id=6
> - adrGroup_Id being unique
>
> So why not :
>
> SELECT * FROM adressGroup
> WHERE Group_Id = 6
> GROUP BY AdrGroup_Id HAVING COUNT(*)=1

No, when you say group_id = 6 then you have already the adresses which belongs to this
group and the result of the 'group by having' statement is always 1.  That's not the solution. Consider the following situation. 
The user sent a letter to the persons which belongs to the adressgroups 1,2 and 3. 
Now he like to send the same letter to the persons which belongs
to adressgroup 6. But It can be that  persons in group 6 belongs 
either to group 1, 2 or 3. So the user wants only those adresses which belongs to adressgroup 6 and do not count to any other group. That's way I tried 
the statement with the Exists clause.

Greetings Herbie

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti                            E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services               Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 1999-11-18 16:37  Ed Loehr <ELOEHR@austin.rr.com>
  0 siblings, 1 reply; 71+ messages in thread

From: Ed Loehr @ 1999-11-18 16:37 UTC (permalink / raw)
  To: pgsql-general

> Date: Wed, 17 Nov 1999 20:53:33 +0100
> From: Herbert Liechti <Herbert.Liechti@thinx.ch>
> Subject: sql question
>
> I have a sql problem which I can't solve. The following table is defined
>
> create table AdressGroup
> (
>    AdrGroup_Id                     INTEGER       NOT NULL
>          DEFAULT NEXTVAL('adrverw_id_seq'),
>    Zeit                            DATETIME      NOT NULL,
>    Group_Id                        INTEGER       NOT NULL,
>    Adress_Id                       INTEGER       NOT NULL
> );
>
> The table assigns adresses to adressgroups.
>
> I'd like to select the adresses which belongs to one specific adressGroup and to no other group. If an adress has more than one entry in the AdressGroup
> table it should not be in the projection.
>
> I tried the following:
>
> SELECT * FROM adressGroup
>  WHERE Group_Id = 6
>    AND EXISTS( SELECT AdrGroup_Id FROM adressGroup ag_alias
>                 WHERE adressGroup.AdrGroup_Id = ag_alias.AdrGroup_Id
>                 GROUP BY AdrGroup_Id HAVING COUNT(*) == 1 );
>
> When I do this I get the following error:
>   ERROR:  rewrite: aggregate column of view must be at rigth side in qual
>   ERROR:  rewrite: aggregate column of view must be at rigth side in qual
>
> Does anybody knows the solution? Thanks
>

Not sure about the error above, other than the apparent typos with "adressGroup", but I think you can get what you're after with this:

SELECT *
FROM AdressGroup ag
WHERE ag.Group_Id = 6 AND NOT EXISTS (
    SELECT AdrGroup_Id
    FROM AddressGroup ag2
    WHERE ag2.AdrGroup_Id = ag.AdrGroup_Id AND ag2.Group_Id <> 6);

Hope that helps.

Ed




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: [GENERAL] Re: sql question
@ 1999-11-18 21:09  Herbert Liechti <Herbert.Liechti@thinx.ch>
  parent: Ed Loehr <ELOEHR@austin.rr.com>
  0 siblings, 1 reply; 71+ messages in thread

From: Herbert Liechti @ 1999-11-18 21:09 UTC (permalink / raw)
  To: pgsql-general; +Cc: Ed Loehr <ELOEHR@austin.rr.com>

Ed Loehr wrote:

> > From: Herbert Liechti <Herbert.Liechti@thinx.ch>
> > Subject: sql question
> >
> > I have a sql problem which I can't solve. The following table is defined
> >
> > create table AdressGroup
> > (
> >    AdrGroup_Id                     INTEGER       NOT NULL
> >          DEFAULT NEXTVAL('adrverw_id_seq'),
> >    Zeit                            DATETIME      NOT NULL,
> >    Group_Id                        INTEGER       NOT NULL,
> >    Adress_Id                       INTEGER       NOT NULL
> > );
> >
> > The table assigns adresses to adressgroups.
> >
> > I'd like to select the adresses which belongs to one specific adressGroup and to no other group. If an adress has more than one entry in the AdressGroup
> > table it should not be in the projection.
> >
> > I tried the following:
> >
> > SELECT * FROM adressGroup
> >  WHERE Group_Id = 6
> >    AND EXISTS( SELECT AdrGroup_Id FROM adressGroup ag_alias
> >                 WHERE adressGroup.AdrGroup_Id = ag_alias.AdrGroup_Id
> >                 GROUP BY AdrGroup_Id HAVING COUNT(*) = 1 );
> >
> > When I do this I get the following error:
> >   ERROR:  rewrite: aggregate column of view must be at rigth side in qual
> >   ERROR:  rewrite: aggregate column of view must be at rigth side in qual
> >
> > Does anybody knows the solution? Thanks
> >
>
> Not sure about the error above, other than the apparent typos with "adressGroup", but I think you can get what you're after with this:
>
> SELECT *
> FROM AdressGroup ag
> WHERE ag.Group_Id = 6 AND NOT EXISTS (
>     SELECT AdrGroup_Id
>     FROM AddressGroup ag2
>     WHERE ag2.AdrGroup_Id = ag.AdrGroup_Id AND ag2.Group_Id <> 6);

The query does not produce the estimated result :-/( It returns all records from
adressgroup 6. It seems that the problem can not be solved in a single sql-statement.
I assume that the above statement joins the table with the alias table with the
subset of adress group 6.
Probably I do it with a temporary table:

Create temp table tempGroup(adrGroup_id integer, groupcounter integer);
INSERT INTO tempGroup
SELECT adrGroup_Id, count(*) FROM adrGroup GROUP BY 1 HAVING COUNT(*) = 1);
SELECT * from adrGroup, tempGroup
 WHERE adrGroup.adrGroup_id = tempGroup.adrGroup_id
   AND adrGroup.group_id = 7;

This is working but without the desired performance :-(

Thanks anyway
Herbie

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti                            E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services               Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~






^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: [GENERAL] Re: sql question
@ 1999-11-19 00:17  Ed Loehr <ELOEHR@austin.rr.com>
  parent: Herbert Liechti <Herbert.Liechti@thinx.ch>
  0 siblings, 1 reply; 71+ messages in thread

From: Ed Loehr @ 1999-11-19 00:17 UTC (permalink / raw)
  To: Herbert Liechti <Herbert.Liechti@thinx.ch>; +Cc: pgsql-general

Herbert Liechti wrote:

> Ed Loehr wrote:
>
> > > From: Herbert Liechti <Herbert.Liechti@thinx.ch>
> > > Subject: sql question
> > >
> > > I have a sql problem which I can't solve. The following table is defined
> > >
> > > create table AdressGroup
> > > (
> > >    AdrGroup_Id                     INTEGER       NOT NULL
> > >          DEFAULT NEXTVAL('adrverw_id_seq'),
> > >    Zeit                            DATETIME      NOT NULL,
> > >    Group_Id                        INTEGER       NOT NULL,
> > >    Adress_Id                       INTEGER       NOT NULL
> > > );
> > >
> > > The table assigns adresses to adressgroups.
> > >
> > > I'd like to select the adresses which belongs to one specific adressGroup and to no other group. If an adress has more than one entry in the AdressGroup
> > > table it should not be in the projection.
> > >
> > > I tried the following:
> > >
> > > SELECT * FROM adressGroup
> > >  WHERE Group_Id = 6
> > >    AND EXISTS( SELECT AdrGroup_Id FROM adressGroup ag_alias
> > >                 WHERE adressGroup.AdrGroup_Id = ag_alias.AdrGroup_Id
> > >                 GROUP BY AdrGroup_Id HAVING COUNT(*) = 1 );
> > >
> > > When I do this I get the following error:
> > >   ERROR:  rewrite: aggregate column of view must be at rigth side in qual
> > >   ERROR:  rewrite: aggregate column of view must be at rigth side in qual
> > >
> > > Does anybody knows the solution? Thanks
> > >
> >
> > Not sure about the error above, other than the apparent typos with "adressGroup", but I think you can get what you're after with this:
> >
> > SELECT *
> > FROM AdressGroup ag
> > WHERE ag.Group_Id = 6 AND NOT EXISTS (
> >     SELECT AdrGroup_Id
> >     FROM AddressGroup ag2
> >     WHERE ag2.AdrGroup_Id = ag.AdrGroup_Id AND ag2.Group_Id <> 6);
>
> The query does not produce the estimated result :-/( It returns all records from
> adressgroup 6. It seems that the problem can not be solved in a single sql-statement.
> I assume that the above statement joins the table with the alias table with the
> subset of adress group 6.
> Probably I do it with a temporary table:
>
> Create temp table tempGroup(adrGroup_id integer, groupcounter integer);
> INSERT INTO tempGroup
> SELECT adrGroup_Id, count(*) FROM adrGroup GROUP BY 1 HAVING COUNT(*) = 1);
> SELECT * from adrGroup, tempGroup
>  WHERE adrGroup.adrGroup_id = tempGroup.adrGroup_id
>    AND adrGroup.group_id = 7;
>
> This is working but without the desired performance :-(
>

I meant to use Address_Id where I used adrGroup_id.  Sorry 'bout that.  Try this, it seems to work:

SELECT *
FROM AddressGroup ag1
WHERE NOT EXISTS (
        SELECT *
        FROM AddressGroup ag2
        WHERE ag1.Address_Id = ag2.Address_Id AND
              ag1.Group_Id <> ag2.Group_Id);

A working demonstration is below.

Cheers,
Ed

mydb=> create sequence adrverw_id_seq;
CREATE
mydb=> create table AddressGroup
mydb-> (
mydb->         AdrGroup_Id INTEGER NOT NULL DEFAULT NEXTVAL('adrverw_id_seq'),
mydb->         Zeit        DATETIME NOT NULL,
mydb->         Group_Id    INTEGER NOT NULL,
mydb->         Address_Id  INTEGER NOT NULL
mydb-> );
CREATE
mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',5,1);
NOTICE:  adrverw_id_seq.nextval: sequence was re-created
NOTICE:  adrverw_id_seq.nextval: sequence was re-created
INSERT 3669904 1
mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',6,1);
INSERT 3669905 1
mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',6,2);
INSERT 3669906 1
mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',6,3);
INSERT 3669907 1
mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',7,3);
INSERT 3669908 1
mydb=>
mydb=> SELECT * FROM AddressGroup;
adrgroup_id|zeit                        |group_id|address_id
-----------+----------------------------+--------+----------
          1|Thu Nov 18 18:10:50 1999 CST|       5|         1
          2|Thu Nov 18 18:10:50 1999 CST|       6|         1
          3|Thu Nov 18 18:10:50 1999 CST|       6|         2
          4|Thu Nov 18 18:10:50 1999 CST|       6|         3
          5|Thu Nov 18 18:10:50 1999 CST|       7|         3
(5 rows)

mydb=>
mydb=> SELECT *
mydb-> FROM AddressGroup ag1
mydb-> WHERE NOT EXISTS (
mydb->         SELECT *
mydb->         FROM AddressGroup ag2
mydb->         WHERE ag1.Address_Id = ag2.Address_Id AND
mydb->               ag1.Group_Id <> ag2.Group_Id);
adrgroup_id|zeit                        |group_id|address_id
-----------+----------------------------+--------+----------
          3|Thu Nov 18 18:10:50 1999 CST|       6|         2
(1 row)





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: [GENERAL] Re: sql question
@ 1999-11-19 07:06  Herbert Liechti <Herbert.Liechti@thinx.ch>
  parent: Ed Loehr <ELOEHR@austin.rr.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Herbert Liechti @ 1999-11-19 07:06 UTC (permalink / raw)
  To: Ed Loehr <ELOEHR@austin.rr.com>; pgsql-general

Ed Loehr wrote:

>
> >
> > Create temp table tempGroup(adrGroup_id integer, groupcounter integer);
> > INSERT INTO tempGroup
> > SELECT adrGroup_Id, count(*) FROM adrGroup GROUP BY 1 HAVING COUNT(*) = 1);
> > SELECT * from adrGroup, tempGroup
> >  WHERE adrGroup.adrGroup_id = tempGroup.adrGroup_id
> >    AND adrGroup.group_id = 7;
> >
> > This is working but without the desired performance :-(
> >
>
> I meant to use Address_Id where I used adrGroup_id.  Sorry 'bout that.  Try this, it seems to work:
>
> SELECT *
> FROM AddressGroup ag1
> WHERE NOT EXISTS (
>         SELECT *
>         FROM AddressGroup ag2
>         WHERE ag1.Address_Id = ag2.Address_Id AND
>               ag1.Group_Id <> ag2.Group_Id);
>
> A working demonstration is below.
>
> Cheers,
> Ed
>
> mydb=> create sequence adrverw_id_seq;
> CREATE
> mydb=> create table AddressGroup
> mydb-> (
> mydb->         AdrGroup_Id INTEGER NOT NULL DEFAULT NEXTVAL('adrverw_id_seq'),
> mydb->         Zeit        DATETIME NOT NULL,
> mydb->         Group_Id    INTEGER NOT NULL,
> mydb->         Address_Id  INTEGER NOT NULL
> mydb-> );
> CREATE
> mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',5,1);
> NOTICE:  adrverw_id_seq.nextval: sequence was re-created
> NOTICE:  adrverw_id_seq.nextval: sequence was re-created
> INSERT 3669904 1
> mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',6,1);
> INSERT 3669905 1
> mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',6,2);
> INSERT 3669906 1
> mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',6,3);
> INSERT 3669907 1
> mydb=> insert into AddressGroup(Zeit,Group_Id,Address_Id) values ('now',7,3);
> INSERT 3669908 1
> mydb=>
> mydb=> SELECT * FROM AddressGroup;
> adrgroup_id|zeit                        |group_id|address_id
> -----------+----------------------------+--------+----------
>           1|Thu Nov 18 18:10:50 1999 CST|       5|         1
>           2|Thu Nov 18 18:10:50 1999 CST|       6|         1
>           3|Thu Nov 18 18:10:50 1999 CST|       6|         2
>           4|Thu Nov 18 18:10:50 1999 CST|       6|         3
>           5|Thu Nov 18 18:10:50 1999 CST|       7|         3
> (5 rows)
>
> mydb=>
> mydb=> SELECT *
> mydb-> FROM AddressGroup ag1
> mydb-> WHERE NOT EXISTS (
> mydb->         SELECT *
> mydb->         FROM AddressGroup ag2
> mydb->         WHERE ag1.Address_Id = ag2.Address_Id AND
> mydb->               ag1.Group_Id <> ag2.Group_Id);
> adrgroup_id|zeit                        |group_id|address_id
> -----------+----------------------------+--------+----------
>           3|Thu Nov 18 18:10:50 1999 CST|       6|         2
> (1 row)

It is working ;-) Thanks

Herbie

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti                            E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services               Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~






^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* sql question
@ 2000-01-14 22:16  rut <rutl@earthlink.net>
  0 siblings, 0 replies; 71+ messages in thread

From: rut @ 2000-01-14 22:16 UTC (permalink / raw)
  To: pgsql-questions@postgresql.org

Using vb6 and ado 2.1 if I open a connection string like so:

Public Sub setup()


    Dim cnn1 As ADODB.Connection
    Dim rstEmployees As ADODB.Recordset
    Dim strCnn As String
    Dim varDate As Variant

    ' Open connection.
        strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Database=db; "
    Set cnn1 = New ADODB.Connection
    cnn1.Open strCnn


End Sub

Will this code still try to send the username (machine name) to the sql
server for authentication even though it is not explicitly sent here?

Also, If the Provider is not specified, does the system revert to an
odbc driver vs the ole driver?

Please email if possible:

 rutledgj@earthlink.net




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL question
@ 2000-07-10 10:37  Travis Bauer <trbauer@indiana.edu>
  0 siblings, 1 reply; 71+ messages in thread

From: Travis Bauer @ 2000-07-10 10:37 UTC (permalink / raw)
  To: pgsql-general

Is it possible to return a column in an sql statement which concantenates
two other columns and adds some other characters.  For example:

create table t1 (x int, y int);
insert into t1 values(1,2);
select ???? as nifty from t1;

nifty
-----
1/2

What should I put in for the ???? ?

----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question
@ 2000-07-10 12:27  NRonayette <r97m10@socotec.fr>
  parent: Travis Bauer <trbauer@indiana.edu>
  0 siblings, 0 replies; 71+ messages in thread

From: NRonayette @ 2000-07-10 12:27 UTC (permalink / raw)
  To: Travis Bauer <trbauer@indiana.edu>; +Cc: pgsql-general



Travis Bauer a écrit :
> 
> Is it possible to return a column in an sql statement which concantenates
> two other columns and adds some other characters.  For example:
> 
> create table t1 (x int, y int);
> insert into t1 values(1,2);
> select ???? as nifty from t1;
> 
> nifty
> -----
> 1/2
> 
> What should I put in for the ???? ?

select t1.x||'/'||t1.y from t1;

this should work. If you can't concatenate int with char (t1.x with
'/'), you must use a function to convert int in char If "to_char" is the
name of this function, ???? became : to_char(t1.x)||'/'||to_char(t1.y)

Nicolas



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* sql question
@ 2000-07-12 22:33  Travis Bauer <trbauer@indiana.edu>
  0 siblings, 1 reply; 71+ messages in thread

From: Travis Bauer @ 2000-07-12 22:33 UTC (permalink / raw)
  To: pgsql-general


Let's say I have a table t1 with two fields, x and y.  How do I write an
sql statement like:

select x if y>1 else 0 from t1;

For every record in which y is > 1, you get x back.  Otherwise, you get 0
returned.

Thanks,
----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2000-07-12 23:25  Ross J. Reedstrom <reedstrm@rice.edu>
  parent: Travis Bauer <trbauer@indiana.edu>
  0 siblings, 1 reply; 71+ messages in thread

From: Ross J. Reedstrom @ 2000-07-12 23:25 UTC (permalink / raw)
  To: pgsql-general

On Wed, Jul 12, 2000 at 05:33:09PM -0500, Travis Bauer wrote:
> 
> Let's say I have a table t1 with two fields, x and y.  How do I write an
> sql statement like:
> 
> select x if y>1 else 0 from t1;

SELECT CASE WHEN y>1 THEN x ELSE 0 END FROM t1;



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2000-07-13 10:18  Travis Bauer <trbauer@indiana.edu>
  parent: Ross J. Reedstrom <reedstrm@rice.edu>
  0 siblings, 0 replies; 71+ messages in thread

From: Travis Bauer @ 2000-07-13 10:18 UTC (permalink / raw)
  To: Ross J. Reedstrom <reedstrm@rice.edu>; +Cc: pgsql-general

Thanks,

Now that you mention it, I even remember reading it in the book!

----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------

On Wed, 12 Jul 2000, Ross J. Reedstrom wrote:

> 
> SELECT CASE WHEN y>1 THEN x ELSE 0 END FROM t1;
> 
> >From page 33 of Bruce's book, at:




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* sql question
@ 2000-09-27 19:11  chris markiewicz <cmarkiew@commnav.com>
  0 siblings, 0 replies; 71+ messages in thread

From: chris markiewicz @ 2000-09-27 19:11 UTC (permalink / raw)
  To: pgsql-general

hello

i just learned that some of the constraint altering functionality is not
completely implemented.  as a workaround, i selected all of the data from
that table into a temp table, dropped and recreated the table (with the
correct constraints), and now i want to re-insert the data.  can this be
done with a single sql stmt?

thanks
chris




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2000-09-27 19:20  Adam Lang <aalang@rutgersinsurance.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Adam Lang @ 2000-09-27 19:20 UTC (permalink / raw)
  To: pgsql-general

 yeah

 insert into newtable (column1, column2, etc.)  select * from temptable;

 Or however you need it.

 Here are the specifics:

 http://www.postgresql.org/docs/postgres/sql-insert.htm

 Adam Lang
 Systems Engineer
 Rutgers Casualty Insurance Company
> ----- Original Message -----
> From: "chris markiewicz" <cmarkiew@commnav.com>
> To: "Postgres (E-mail)" <pgsql-general@postgresql.org>
> Sent: Wednesday, September 27, 2000 3:11 PM
> Subject: [GENERAL] sql question
>
>
> > hello
> >
> > i just learned that some of the constraint altering functionality is not
> > completely implemented.  as a workaround, i selected all of the data
from
> > that table into a temp table, dropped and recreated the table (with the
> > correct constraints), and now i want to re-insert the data.  can this be
> > done with a single sql stmt?
> >
> > thanks
> > chris
>




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL question
@ 2000-11-23 15:23  Igor' Robul' <igor@raduga.dyndns.org>
  0 siblings, 0 replies; 71+ messages in thread

From: Igor' Robul' @ 2000-11-23 15:23 UTC (permalink / raw)
  To: pgsql-general

Hello, 

For example:
SELECT words.id, files.id FROM words,files WHERE w=$1 and host=$2 and name=$3;

If words table has $1 and files table has $2 and $3 then I'll get
result like
id1 id2
-------
223 235

But if words or files does not have corresponding info then it will
return empty relation.

Is it possible get something like
id1 id2
--------
234 NULL

Thank you for info.

-- 
Igor' Robul', Unix System Administrator & Programmer @ sanatorium "Raduga", 
Sochi, Russia
http://www.brainbench.com/transcript.jsp?pid=304744



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL Question
@ 2001-02-22 21:13  Matthew <matt@ctlno.com>
  0 siblings, 1 reply; 71+ messages in thread

From: Matthew @ 2001-02-22 21:13 UTC (permalink / raw)
  To: pgsql-general

In 7.0.3 I want to do:

select count( distinct browser, ssn) from applicant_sessions where browser
like '%5.5%';

But I get this error:

ERROR:  Aggregate functions may only have one parameter

In 7.1 I can do:
select count(*) from (select distinct browser, ssn from applicant_sessions
where browser like '%5.5%') as distinctbrow;

to get the desired result, but I can't do that in 7.0.x.  Any suggestions?
Am I doing anything wrong with my sql syntax.  I'm working around it right
now by doing a pg_numrows from PHP after I do the first select, but I would
like to get the count directly as it will be faster.

Thanks,

Matt



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2001-02-22 21:37  Mitch Vincent <mitch@venux.net>
  parent: Matthew <matt@ctlno.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Mitch Vincent @ 2001-02-22 21:37 UTC (permalink / raw)
  To: Matthew <matt@ctlno.com>; pgsql-general

> select count( distinct browser, ssn) from applicant_sessions where browser
> like '%5.5%';
>
> But I get this error:
>
> ERROR:  Aggregate functions may only have one parameter

To be expected for count, I think..

> In 7.1 I can do:
> select count(*) from (select distinct browser, ssn from applicant_sessions
> where browser like '%5.5%') as distinctbrow;
>
> to get the desired result, but I can't do that in 7.0.x.  Any suggestions?
> Am I doing anything wrong with my sql syntax.  I'm working around it right
> now by doing a pg_numrows from PHP after I do the first select, but I
would
> like to get the count directly as it will be faster.

Are you not doing the query SELECT * FROM applicant_sessions WHERE browser
LIKE '%5.5%'  ? If you're not then yes, selecting just the count will be
faster but if you're already doing the query then why not just see how many
rows are returned by it.. It's one less step if you have to run the query
anyway..

> select count(*) from (select distinct browser, ssn from applicant_sessions
> where browser like '%5.5%') as distinctbrow;

That would be slower than just the inner SELECT by itself, so you're not
gaining anything even by doing that in 7.1.. You're still executing that
sub-select and doing processing against it.. *shrug*

Just some thoughts.. Good luck..

-Mitch





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL Question
@ 2001-03-08 18:36  Matthew <matt@ctlno.com>
  0 siblings, 1 reply; 71+ messages in thread

From: Matthew @ 2001-03-08 18:36 UTC (permalink / raw)
  To: pgsql-general

I'm trying to do some type of conditional select statement and I have no
idea how.

What I would like is something like this (using pseudo sql)

select (if(col1>1) true else if (col1<= 1) false) from foo;

does that make sense?  Basically I want one column of my result set to be
true or false based on some logic.  I can do it in code and have my program
handle it, but I was hoping to do it in SQL.

Thanks,

Matt O'Connor



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2001-03-08 20:13  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Matthew <matt@ctlno.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Tom Lane @ 2001-03-08 20:13 UTC (permalink / raw)
  To: Matthew <matt@ctlno.com>; +Cc: pgsql-general

Matthew <matt@ctlno.com> writes:
> What I would like is something like this (using pseudo sql)
> select (if(col1>1) true else if (col1<= 1) false) from foo;

This particular example could be written as just

	select col1>1 from foo

since a boolean result is a perfectly good result.

More generally, you could use the CASE construct for conditional
expressions.  See
http://www.postgresql.org/devel-corner/docs/postgres/functions-conditional.html

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* sql question
@ 2001-05-13 18:45  u95886230@spawnkill.ip-mobilphone.net
  0 siblings, 3 replies; 71+ messages in thread

From: u95886230@spawnkill.ip-mobilphone.net @ 2001-05-13 18:45 UTC (permalink / raw)
  To: pgsql-general

Please help, I am trying to write an SQL statement but with no success as I am just 
starting out with sql.

I have a table with 3 columns: Account# ,OrderType and date
example of data:
Account#    &brvbar;   Ordertype  &brvbar; Date
1           &brvbar;   A          &brvbar; April
1           &brvbar;   B          &brvbar; May
1           &brvbar;   B          &brvbar; May
2           &brvbar;   B          &brvbar; April
2           &brvbar;   B          &brvbar; May
2           &brvbar;   C          &brvbar; May
3           &brvbar;   C          &brvbar; May


I need to write a select that will show me the totals of EACH type for EACH account AND 
total ordersplaced for a SPECIFIC month eg..Show me the results for May...

account &brvbar; TotA  &brvbar; TotB  &brvbar; TotC  &brvbar; Total
1       &brvbar; 0     &brvbar; 2     &brvbar; 0     &brvbar; 2
2       &brvbar; 0     &brvbar; 1     &brvbar; 1     &brvbar; 2
3       &brvbar; 0     &brvbar; 0     &brvbar; 1     &brvbar; 1

I can use temp tables, but need a solution written as basic as pssible so I can understand 
it (all in the form select this from that)
any help would be fantastic as I am completely stuck and have been trying for about a week

thanks
sgebbie@ciaoweb.it
 



-- 
Sent by sgebbie from  ciaoweb piece from  it
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2001-05-15 10:45  Justin Clift <justin@postgresql.org>
  parent: u95886230@spawnkill.ip-mobilphone.net
  2 siblings, 0 replies; 71+ messages in thread

From: Justin Clift @ 2001-05-15 10:45 UTC (permalink / raw)
  To: u95886230@spawnkill.ip-mobilphone.net; pgsql-general

Hi,

I don't know if it's useful, but there are a number of SQL tutorials
available at
http://www.newbienetwork.net/sections.php?op=listarticles&secid=5 and
there is a list of all sorts of useful PostgreSQL & SQL resources
(including tutorials) at http://techdocs.postgresql.org/oresources.php

:-)

Regards and best wishes,

Justin Clift

u95886230@spawnkill.ip-mobilphone.net wrote:
> 
> Please help, I am trying to write an SQL statement but with no success as I am just
> starting out with sql.
> 
> I have a table with 3 columns: Account# ,OrderType and date
> example of data:
> Account#    &brvbar;   Ordertype  &brvbar; Date
> 1           &brvbar;   A          &brvbar; April
> 1           &brvbar;   B          &brvbar; May
> 1           &brvbar;   B          &brvbar; May
> 2           &brvbar;   B          &brvbar; April
> 2           &brvbar;   B          &brvbar; May
> 2           &brvbar;   C          &brvbar; May
> 3           &brvbar;   C          &brvbar; May
> 
> I need to write a select that will show me the totals of EACH type for EACH account AND
> total ordersplaced for a SPECIFIC month eg..Show me the results for May...
> 
> account &brvbar; TotA  &brvbar; TotB  &brvbar; TotC  &brvbar; Total
> 1       &brvbar; 0     &brvbar; 2     &brvbar; 0     &brvbar; 2
> 2       &brvbar; 0     &brvbar; 1     &brvbar; 1     &brvbar; 2
> 3       &brvbar; 0     &brvbar; 0     &brvbar; 1     &brvbar; 1
> 
> I can use temp tables, but need a solution written as basic as pssible so I can understand
> it (all in the form select this from that)
> any help would be fantastic as I am completely stuck and have been trying for about a week
> 
> thanks
> sgebbie@ciaoweb.it
> 
> 
> --
> Sent by sgebbie from  ciaoweb piece from  it
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2001-05-16 05:26  will trillich <will@serensoft.com>
  parent: u95886230@spawnkill.ip-mobilphone.net
  2 siblings, 1 reply; 71+ messages in thread

From: will trillich @ 2001-05-16 05:26 UTC (permalink / raw)
  To: pgsql-general

On Sun, May 13, 2001 at 06:45:05PM +0000, u95886230@spawnkill.ip-mobilphone.net wrote:
> I have a table with 3 columns: Account# ,OrderType and date
> example of data:
> Account#    &brvbar;   Ordertype  &brvbar; Date
> 1           &brvbar;   A          &brvbar; April
> 1           &brvbar;   B          &brvbar; May
> 1           &brvbar;   B          &brvbar; May
> 2           &brvbar;   B          &brvbar; April
> 2           &brvbar;   B          &brvbar; May
> 2           &brvbar;   C          &brvbar; May
> 3           &brvbar;   C          &brvbar; May
> 
> 
> I need to write a select that will show me the totals of EACH type for EACH account AND 
> total ordersplaced for a SPECIFIC month eg..Show me the results for May...
> 
> account &brvbar; TotA  &brvbar; TotB  &brvbar; TotC  &brvbar; Total
> 1       &brvbar; 0     &brvbar; 2     &brvbar; 0     &brvbar; 2
> 2       &brvbar; 0     &brvbar; 1     &brvbar; 1     &brvbar; 2
> 3       &brvbar; 0     &brvbar; 0     &brvbar; 1     &brvbar; 1
> 
> I can use temp tables, but need a solution written as basic as pssible so I can understand 
> it (all in the form select this from that)
> any help would be fantastic as I am completely stuck and have been trying for about a week

don't you hate that? :)

sql is not like perl or basic in that you can start this over
here, assign a variable, break your output into chunks and do
various voodoo to it before it comes out. all sql does is ask
for records from a database, to return them in a certain order
or in certain groups.

	select city,state,count(id),sum(id)
		from mycontacts
		group by state,city;

that'll show you something like

	chicago     il    12   345
	evanston    il    3    121
	springfield il    9    208
	evansville  in    5    184
	jasper      in    4    166
	terre haute in    6    201
	henderson   ky    5    190
	louisville  ky    8    124

if you're pulling those results into a program (php? perl?) then
you can do interesting things such as output a break on state
boundaries to compute state subtotals, and so forth.

of course, there are devious individuals out there who don't
know how to play by the rules and they make sql do some amazing
handsprings. but those people are not much fun at parties.
(and seldom attend, anyway.)

-- 
What do I need manners for? I already got me a wife.
	-- Adam Pontipee, "Seven Brides for Seven Brothers"

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2001-05-16 20:03  Joel Burton <jburton@scw.org>
  parent: will trillich <will@serensoft.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Joel Burton @ 2001-05-16 20:03 UTC (permalink / raw)
  To: will trillich <will@serensoft.com>; +Cc: pgsql-general

On Wed, 16 May 2001, will trillich wrote:

> On Sun, May 13, 2001 at 06:45:05PM +0000, u95886230@spawnkill.ip-mobilphone.net wrote:
> > I have a table with 3 columns: Account# ,OrderType and date
> > example of data:
> > Account#    &brvbar;   Ordertype  &brvbar; Date
> > 1           &brvbar;   A          &brvbar; April
> > 1           &brvbar;   B          &brvbar; May
> > 1           &brvbar;   B          &brvbar; May
> > 2           &brvbar;   B          &brvbar; April
> > 2           &brvbar;   B          &brvbar; May
> > 2           &brvbar;   C          &brvbar; May
> > 3           &brvbar;   C          &brvbar; May
> > 
> > 
> > I need to write a select that will show me the totals of EACH type for EACH account AND 
> > total ordersplaced for a SPECIFIC month eg..Show me the results for May...
> > 
> > account &brvbar; TotA  &brvbar; TotB  &brvbar; TotC  &brvbar; Total
> > 1       &brvbar; 0     &brvbar; 2     &brvbar; 0     &brvbar; 2
> > 2       &brvbar; 0     &brvbar; 1     &brvbar; 1     &brvbar; 2
> > 3       &brvbar; 0     &brvbar; 0     &brvbar; 1     &brvbar; 1
> > 
> > I can use temp tables, but need a solution written as basic as pssible so I can understand 
> > it (all in the form select this from that)
> > any help would be fantastic as I am completely stuck and have been trying for about a week
> 
> if you're pulling those results into a program (php? perl?) then
> you can do interesting things such as output a break on state
> boundaries to compute state subtotals, and so forth.

You can do it in SQL, with something like:

SELECT accountnum, (SELECT count(*) FROM data a WHERE a.accounttype='a'
and a.accountnum=o.accountnum) AS TotA, .. < same for B and C > .. FROM
data o group by accoutnum;

It won't be terribly fast, but, then, getting all this data into Python or
Perl and doing it there won't be so speedy either.

HTH,
-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2001-05-21 00:44  Blackin <rblackin@home.com>
  parent: u95886230@spawnkill.ip-mobilphone.net
  2 siblings, 0 replies; 71+ messages in thread

From: Blackin @ 2001-05-21 00:44 UTC (permalink / raw)
  To: pgsql-general

select sum(case when ordertype='A' then 1 else 0 end) as TotA,
sum(case when ordertype='B' then 1 else 0 end) as TotB,
sum(case when ordertype='C' then 1 else 0 end) as TotC
From Thetable
where Date="May"

This works if you know how many columns(OrderTypes) that you have.  If you
do not know this query can be built dynamically.

Rob


<u95886230@spawnkill.ip-mobilphone.net> wrote in message
news:l.989779505.1960693359@[212.171.170.132]...
> Please help, I am trying to write an SQL statement but with no success as
I am just
> starting out with sql.
>
> I have a table with 3 columns: Account# ,OrderType and date
> example of data:
> Account#    &brvbar;   Ordertype  &brvbar; Date
> 1           &brvbar;   A          &brvbar; April
> 1           &brvbar;   B          &brvbar; May
> 1           &brvbar;   B          &brvbar; May
> 2           &brvbar;   B          &brvbar; April
> 2           &brvbar;   B          &brvbar; May
> 2           &brvbar;   C          &brvbar; May
> 3           &brvbar;   C          &brvbar; May
>
>
> I need to write a select that will show me the totals of EACH type for
EACH account AND
> total ordersplaced for a SPECIFIC month eg..Show me the results for May...
>
> account &brvbar; TotA  &brvbar; TotB  &brvbar; TotC  &brvbar; Total
> 1       &brvbar; 0     &brvbar; 2     &brvbar; 0     &brvbar; 2
> 2       &brvbar; 0     &brvbar; 1     &brvbar; 1     &brvbar; 2
> 3       &brvbar; 0     &brvbar; 0     &brvbar; 1     &brvbar; 1
>
> I can use temp tables, but need a solution written as basic as pssible so
I can understand
> it (all in the form select this from that)
> any help would be fantastic as I am completely stuck and have been trying
for about a week
>
> thanks
> sgebbie@ciaoweb.it
>
>
>
>
> --
> Sent by sgebbie from  ciaoweb piece from  it
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL question
@ 2001-11-02 01:17  Johnson, Shaunn <SJohnson6@bcbsm.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Johnson, Shaunn @ 2001-11-02 01:17 UTC (permalink / raw)
  To: pgsql-general

Howdy:

Not sure if this is the correct group - let me now
if I should post elsewhere ...

Anyhow, I'm new with SQL.  I wrote a script that I *thought*
should work.  What I'm trying to do is query 5 tables from 5 
different databases that have the same columns and collect all 
of the information and append it into one file.  This is the script:

[script]

SELECT
C_CONTRACT_NUM AS CONTRACT,
C_MBR_NUM AS MBR_NUM,
C_CLASS AS CLASS,
MAX(D_END_DT) AS DENDDT
FROM database1.table1_CN2PCLAS V
GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM
HAVING MAX(D_END_DT)=(
	SELECT MAX(D_END_DT)
	FROM database1.table1_CN2PCLAS W
	WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM
	AND V.C_MBR_NUM = W.C_MBR_NUM
	AND D_END_DT >= '2000-01-01')
UNION
SELECT
C_CONTRACT_NUM AS CONTRACT,
C_MBR_NUM AS MBR_NUM,
C_CLASS AS CLASS,
MAX(D_END_DT) AS DENDDT
FROM database2.table1_CN2PCLAS V
GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM
HAVING MAX(D_END_DT)=(
	SELECT MAX(D_END_DT)
	FROM database2.table1_CN2PCLAS W
	WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM
	AND V.C_MBR_NUM = W.C_MBR_NUM
	AND D_END_DT >= '2000-01-01')
UNION
SELECT
C_CONTRACT_NUM AS CONTRACT,
C_MBR_NUM AS MBR_NUM,
C_CLASS AS CLASS,
MAX(D_END_DT) AS DENDDT
FROM database3.table1_CN2PCLAS V
GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM
HAVING MAX(D_END_DT)=(
	SELECT MAX(D_END_DT)
	FROM database3.table1_CN2PCLAS W
	WHERE D_END_DT >= '2000-01-01')
	WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM
	AND V.C_MBR_NUM = W.C_MBR_NUM
	AND D_END_DT >= '2000-01-01')
UNION
SELECT
C_CONTRACT_NUM AS CONTRACT,
C_MBR_NUM AS MBR_NUM,
C_CLASS AS CLASS,
MAX(D_END_DT) AS DENDDT
FROM database4.table1_CN2PCLAS V
GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM
HAVING MAX(D_END_DT)=(
	SELECT MAX(D_END_DT)
	FROM database4.table1_CN2PCLAS W
	WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM
	AND V.C_MBR_NUM = W.C_MBR_NUM
	AND D_END_DT >= '2000-01-01')
UNION
SELECT
C_CONTRACT_NUM AS CONTRACT,
C_MBR_NUM AS MBR_NUM,
C_CLASS AS CLASS,
MAX(D_END_DT) AS DENDDT
FROM database5.table1_CN2PCLAS V
GROUP BY C_CLASS, C_CONTRACT_NUM, C_MBR_NUM
HAVING MAX(D_END_DT)=(
	SELECT MAX(D_END_DT)
	FROM database5.table1_CN2PCLAS W
	WHERE V.C_CONTRACT_NUM = W.C_CONTRACT_NUM
	AND V.C_MBR_NUM = W.C_MBR_NUM
	AND D_END_DT >= '2000-01-01');

[/script]

But the error I'm getting is:

[error]

The use of the reserved word "WHERE" following "" is not valid.
Expected tokens may include:  "FOR WITH ORDER UNION EXCEPT QUERYNO
OPTIMIZE".

[/error]

I'm not clear on the meaning of that error.  Can someone re-explain
what it's trying to say and why the query isn't working?

Thanks!

-X

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL question..
@ 2002-04-17 16:50  Alexis Maldonado <amaldona@ctcd.cc.tx.us>
  0 siblings, 1 reply; 71+ messages in thread

From: Alexis Maldonado @ 2002-04-17 16:50 UTC (permalink / raw)
  To: pgsql-general

I know this might be a dumb basic sql question but..

I have a query list of IDs ( 4,3,1,2,12,5,6,8,7,9,11)

I want to build a query that gets those IDs in the exact same order but when i do

Select * From Table
Where ID IN ( 4,3,1,2,12,5,6,8,7,9,11)

It gets all the IDs in random order..

how can i get them in the order listed?

Thanks,
Alex

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question..
@ 2002-04-18 08:05  Petr Bravenec <pbravenec@solartec.cz>
  parent: Alexis Maldonado <amaldona@ctcd.cc.tx.us>
  0 siblings, 0 replies; 71+ messages in thread

From: Petr Bravenec @ 2002-04-18 08:05 UTC (permalink / raw)
  To: pgsql-general

It looks like an impossible thing in SQL. The only way to achieve
this may be an temporary table with added sorting information:

create temporary table tpmsort (linenum serial, id int4);
insert into tmpsort (id) values (4);
insert into tmpsort (id) values (3);
insert into tmpsort (id) values (1);
insert into tmpsort (id) values (2); .... in the order you requested

and then

select * from table, tmpsort
   where table.id = tmpsort.id
   order by tmpsort.linenum;

Petr Bravenec

Alexis Maldonado wrote:

>  
>
> I have a query list of IDs ( 4,3,1,2,12,5,6,8,7,9,11)
>
>  
>
> I want to build a query that gets those IDs in the exact same order 
> but when i do
>
>  
>
> Select * From Table
>
> Where ID IN ( 4,3,1,2,12,5,6,8,7,9,11)
>
>  
>
> It gets all the IDs in random order..
>
>  
>
> how can i get them in the order listed?
>





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL question
@ 2003-06-09 13:30  Guillaume Houssay <ghoussay@noos.fr>
  0 siblings, 2 replies; 71+ messages in thread

From: Guillaume Houssay @ 2003-06-09 13:30 UTC (permalink / raw)
  To: pgsql-general

I found a way to have the list of all the tables for my database.

Now, I would like to get the name of the columns of any table.
I could not find the SQL command to do that.

Thank you for you help

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question
@ 2003-06-10 05:32  Martijn van Oosterhout <kleptog@svana.org>
  parent: Guillaume Houssay <ghoussay@noos.fr>
  1 sibling, 0 replies; 71+ messages in thread

From: Martijn van Oosterhout @ 2003-06-10 05:32 UTC (permalink / raw)
  To: Guillaume Houssay <ghoussay@noos.fr>; +Cc: pgsql-general

On Mon, Jun 09, 2003 at 03:30:33PM +0200, Guillaume Houssay wrote:
> I found a way to have the list of all the tables for my database.
> 
> Now, I would like to get the name of the columns of any table.
> I could not find the SQL command to do that.

In psql type \? to get a list of all the commands. In particular, \d
<tablename> is what you're looking for.

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question
@ 2003-06-10 07:04  Richard Huxton <dev@archonet.com>
  parent: Guillaume Houssay <ghoussay@noos.fr>
  1 sibling, 0 replies; 71+ messages in thread

From: Richard Huxton @ 2003-06-10 07:04 UTC (permalink / raw)
  To: Guillaume Houssay <ghoussay@noos.fr>; pgsql-general

On Monday 09 Jun 2003 2:30 pm, Guillaume Houssay wrote:
> I found a way to have the list of all the tables for my database.
>
> Now, I would like to get the name of the columns of any table.
> I could not find the SQL command to do that.
>
> Thank you for you help

In psql there are a number of backslash commands - you might find the 
following useful:

\d
\dt
\dv
\d mytable1
\d my*

If you start psql with the -E flag you'll see the SQL that's used to run 
these. See "man psql" or the manuals for more info.

-- 
  Richard Huxton



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL question
@ 2003-06-19 16:36  Együd Csaba <csegyud@freemail.hu>
  0 siblings, 1 reply; 71+ messages in thread

From: Együd Csaba @ 2003-06-19 16:36 UTC (permalink / raw)
  To: pgsql-general

Hi All,
I have a problem. I have 3 tables.
1. stock changes
2. product groups
3. a link table between the 2 above

I need the name of the product group the product belongs to, which product
is the subject of the stock change. (I hope it's understandable)

So I tried this query:
----------------------------------------------------------------------------
------------------
DB=# select t_stockchanges.productid, (select name from t_productgroups
where id=(select productgroupid from t_prod_in_pgr where
productid=t_stockchanges.productid)) as pgroup from t_stockchanges;
----------------------------------------------------------------------------
------------------

As I suppose this query should have produced 2 columns, the productid and
the related product group name: pgroup.

But instead it generated an error. It says, that the subquery (I suppose the
most inner) gives back more then 1 tuple. How can I query the only 1 record,
that matches the actual t-stockchanges records productid field. Isn't it
calculated for each stockchanges record?

Thank you in advance.

Best Regards,
-- Csaba




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.489 / Virus Database: 288 - Release Date: 2003. 06. 10.




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question
@ 2003-06-19 18:37  Richard Huxton <dev@archonet.com>
  parent: Együd Csaba <csegyud@freemail.hu>
  0 siblings, 0 replies; 71+ messages in thread

From: Richard Huxton @ 2003-06-19 18:37 UTC (permalink / raw)
  To: Együd Csaba <csegyud@freemail.hu>; pgsql-general

On Thursday 19 Jun 2003 5:36 pm, Együd Csaba wrote:
> Hi All,
> I have a problem. I have 3 tables.
> 1. stock changes
> 2. product groups
> 3. a link table between the 2 above
>
> I need the name of the product group the product belongs to, which product
> is the subject of the stock change. (I hope it's understandable)
>
> So I tried this query:
> ---------------------------------------------------------------------------
>- ------------------
> DB=# select t_stockchanges.productid, (select name from t_productgroups
> where id=(select productgroupid from t_prod_in_pgr where
> productid=t_stockchanges.productid)) as pgroup from t_stockchanges;

Try something like:

SELECT chg.productid, grp.name as pgroup
FROM t_stockchanges chg, t_prod_in_pgr pp, t_productgroups grp
WHERE chg.productid=pp.productid AND pp.productgroupid=grp.id;

I might have got some of your fields wrong, but what I'm trying to do is join 
across the linked fields.

change.product_id => linktbl.product_id, linktbl.group_id => groups.group_id

No need for a subselect here.
-- 
  Richard Huxton



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL  (?) question
@ 2004-07-20 20:31  Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
  0 siblings, 1 reply; 71+ messages in thread

From: Joost Kraaijeveld @ 2004-07-20 20:31 UTC (permalink / raw)
  To: pgsql-general

Hi all,

Is there an easy way (e.g. short SQL script) to add a column to a table and fill it with integers (from 1- n) just like using an sequence? (I need to retofit a system id that is handled in the application, not in the database).

Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl 



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL  (?) question
@ 2004-07-20 21:24  Mike Mascari <mascarm@mascari.com>
  parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
  0 siblings, 0 replies; 71+ messages in thread

From: Mike Mascari @ 2004-07-20 21:24 UTC (permalink / raw)
  To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-general

Joost Kraaijeveld wrote:

> Hi all,
> 
> Is there an easy way (e.g. short SQL script) to add a column to a
> table and fill it with integers (from 1- n) just like using an
> sequence? (I need to retofit a system id that is handled in the
> application, not in the database).

I assume there is some unique candidate key on the table already (or 
worst case, an OID.) If so, I'd just do:

CREATE SEQUENCE my_sequence;

CREATE TEMPORARY TABLE foo AS
SELECT candidate_key, nextval('my_sequence') as new_key
FROM table_that_needs_fixed;

ALTER TABLE table_that_needs_fixed
ADD COLUMN new_key integer;

UPDATE table_that_needs_fixed
SET new_key = foo.new_key
WHERE candidate_key = foo.candidate_key;

Add appropriate constraints as necessary...

HTH,

Mike Mascari





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL question
@ 2004-11-06 23:13  Uwe C. Schroeder <uwe@oss4u.com>
  0 siblings, 4 replies; 71+ messages in thread

From: Uwe C. Schroeder @ 2004-11-06 23:13 UTC (permalink / raw)
  To: pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Here's a question for the SQL guru's out there, which I've been trying to 
solve for the last couple of hours. There's got to be a solution to this, but 
somehow I can't find it.

Tables:

table1 (
	uid int PK,
        uname varchar(64)
)

table2 (
	uid int FK to table1,
        xuid int FK to table 1
)

table3 (
  	uid int FK to table1,
	yuid int FK to table1
)

There might be more tables of the type like table2 and table3, but I'd already 
be happy to solve the puzzle with the 3 tables above.
Ok, assume table1 is the master table - in my case a table used for login 
authentication (some columns removed above)
table2 and table3 are tables where the uid always references to the uid in 
table1. The second "uid" (xuid and yuid in this example) references to 
another uid record in table1. The problem is that there may or may not be 
entries in table2 (or table3) referencing a specific uid in their second uid 
field.
Maybe some data:

table1:
1 test1
2 test2
3 test3

table2:
1 2
1 3
3 1

table3:
1 2
2 3
3 2

What I want to do in a view is the following resultset:

uid  uname xuid yuid
 1    test1     2      2
 1    test1     3
 2    test2             3
 3    test3      1
 3    test3             2


So basically I want to know which uid is connected to which uid, one 
relationship per row. So xuid and yuid shall be identical if records exist in 
both table2 and table3 or the value shall be NULL if a corresponding record 
can't be found in either table2 or table3.

Can anyone here help me out? 

Thanks a lot

	UC

- --
Open Source Solutions 4U, LLC	2570 Fleetwood Drive
Phone:  +1 650 872 2425		San Bruno, CA 94066
Cell:   +1 650 302 2405		United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBjVqVjqGXBvRToM4RAhk5AKCjjGc4VilA45PnPZoKluTNYUP6FACgrRQq
XFIvkCIJHyz7TvvV/XxL4Lk=
=/vxG
-----END PGP SIGNATURE-----




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question
@ 2004-11-07 01:17  Uwe C. Schroeder <uwe@oss4u.com>
  parent: Uwe C. Schroeder <uwe@oss4u.com>
  3 siblings, 0 replies; 71+ messages in thread

From: Uwe C. Schroeder @ 2004-11-07 01:17 UTC (permalink / raw)
  To: pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


If someone knows this it would be great - because I'm still curious how to 
solve it. However I just remodelled my db structure to eliminate the problem 
(basically I pulled the several tables into one since each of the 
table2/table3 tables only has 3 fields)
so now I do:
table2 (
 uid int FK to table1,
 luid int FK to table1,
 is_in_table3 boolean,
 is_in_table4 boolean,
 .....
)

this just needs a simple select with a join against table1.
UC


On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder wrote:
> Here's a question for the SQL guru's out there, which I've been trying to
> solve for the last couple of hours. There's got to be a solution to this,
> but somehow I can't find it.
>
> Tables:
>
> table1 (
> 	uid int PK,
>         uname varchar(64)
> )
>
> table2 (
> 	uid int FK to table1,
>         xuid int FK to table 1
> )
>
> table3 (
>   	uid int FK to table1,
> 	yuid int FK to table1
> )
>
> There might be more tables of the type like table2 and table3, but I'd
> already be happy to solve the puzzle with the 3 tables above.
> Ok, assume table1 is the master table - in my case a table used for login
> authentication (some columns removed above)
> table2 and table3 are tables where the uid always references to the uid in
> table1. The second "uid" (xuid and yuid in this example) references to
> another uid record in table1. The problem is that there may or may not be
> entries in table2 (or table3) referencing a specific uid in their second
> uid field.
> Maybe some data:
>
> table1:
> 1 test1
> 2 test2
> 3 test3
>
> table2:
> 1 2
> 1 3
> 3 1
>
> table3:
> 1 2
> 2 3
> 3 2
>
> What I want to do in a view is the following resultset:
>
> uid  uname xuid yuid
>  1    test1     2      2
>  1    test1     3
>  2    test2             3
>  3    test3      1
>  3    test3             2
>
>
> So basically I want to know which uid is connected to which uid, one
> relationship per row. So xuid and yuid shall be identical if records exist
> in both table2 and table3 or the value shall be NULL if a corresponding
> record can't be found in either table2 or table3.
>
> Can anyone here help me out?
>
> Thanks a lot
>
> 	UC

- --
Open Source Solutions 4U, LLC	2570 Fleetwood Drive
Phone:  +1 650 872 2425		San Bruno, CA 94066
Cell:   +1 650 302 2405		United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBjXebjqGXBvRToM4RAq+kAKCyK1bwfRBdVoAvoi0boELr90wSmwCdHUEf
p9L9Z1OSHwqvYn+ZnDWSTQw=
=Ih7b
-----END PGP SIGNATURE-----




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question
@ 2004-11-07 05:53  Vincent Hikida <vhikida@inreach.com>
  parent: Uwe C. Schroeder <uwe@oss4u.com>
  3 siblings, 0 replies; 71+ messages in thread

From: Vincent Hikida @ 2004-11-07 05:53 UTC (permalink / raw)
  To: Uwe C. Schroeder <uwe@oss4u.com>; pgsql-general

I'm afraid, I'm not used to SQL92 join syntax and almost all my experience
is in Oracle but how about:

SELECT t1.uid
             , t1.xname
             , t2.uid
             , t3.uid
  FROM                         table1  t1
               INNER JOIN table2  t2 ON t1.uid = t2.uid
               INNER JOIN table3  t3 ON t2.uid = t3.uid
 UNION
SELECT t1.uid
              , t1.xname
              , t2.uid
              , NULL
  FROM                         table1  t1
               INNER JOIN table2  t2 ON t1.uid = t2.uid
 WHERE NOT EXISTS
                 (SELECT NULL
                      FROM table3 t3
                   WHERE t3.uid = t1.uid
                 )
 UNION
SELECT t1.uid
              , t1.xname
              , NULL
              , t3.uid
  FROM                          table1 t1
                INNER JOIN table3 t3 ON t1.uid = t3.uid
WHERE NOT EXISTS
                (SELECT NULL
                     FROM table2 t2
                  WHERE t2.uid = t3.uid
                )

Perhaps there was a solution using outer joins and case statements within
the SELECT clause. Perhaps there is also a solution using subselects in the
SELECT clause. However, this is all I can do for tonight.

Vincent

----- Original Message -----
From: "Uwe C. Schroeder" <uwe@oss4u.com>
To: <pgsql-general@postgresql.org>
Sent: Saturday, November 06, 2004 3:13 PM
Subject: [GENERAL] SQL question


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Here's a question for the SQL guru's out there, which I've been trying to
solve for the last couple of hours. There's got to be a solution to this,
but
somehow I can't find it.

Tables:

table1 (
uid int PK,
        uname varchar(64)
)

table2 (
uid int FK to table1,
        xuid int FK to table 1
)

table3 (
  uid int FK to table1,
yuid int FK to table1
)

There might be more tables of the type like table2 and table3, but I'd
already
be happy to solve the puzzle with the 3 tables above.
Ok, assume table1 is the master table - in my case a table used for login
authentication (some columns removed above)
table2 and table3 are tables where the uid always references to the uid in
table1. The second "uid" (xuid and yuid in this example) references to
another uid record in table1. The problem is that there may or may not be
entries in table2 (or table3) referencing a specific uid in their second uid
field.
Maybe some data:

table1:
1 test1
2 test2
3 test3

table2:
1 2
1 3
3 1

table3:
1 2
2 3
3 2

What I want to do in a view is the following resultset:

uid  uname xuid yuid
 1    test1     2      2
 1    test1     3
 2    test2             3
 3    test3      1
 3    test3             2


So basically I want to know which uid is connected to which uid, one
relationship per row. So xuid and yuid shall be identical if records exist
in
both table2 and table3 or the value shall be NULL if a corresponding record
can't be found in either table2 or table3.

Can anyone here help me out?

Thanks a lot

UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone:  +1 650 872 2425 San Bruno, CA 94066
Cell:   +1 650 302 2405 United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBjVqVjqGXBvRToM4RAhk5AKCjjGc4VilA45PnPZoKluTNYUP6FACgrRQq
XFIvkCIJHyz7TvvV/XxL4Lk=
=/vxG
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 3: 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







^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question
@ 2004-11-07 20:16  Greg Stark <gsstark@mit.edu>
  parent: Uwe C. Schroeder <uwe@oss4u.com>
  3 siblings, 0 replies; 71+ messages in thread

From: Greg Stark @ 2004-11-07 20:16 UTC (permalink / raw)
  To: pgsql-general

"Uwe C. Schroeder" <uwe@oss4u.com> writes:

> Here's a question for the SQL guru's out there, which I've been trying to 
> solve for the last couple of hours. There's got to be a solution to this, but 
> somehow I can't find it.
> 
> Tables:
> 
> table1 (
> 	uid int PK,
>         uname varchar(64)
> )
> 
> table2 (
> 	uid int FK to table1,
>         xuid int FK to table 1
> )
> 
> table3 (
>   	uid int FK to table1,
> 	yuid int FK to table1
> )

SELECT * 
  FROM table2 
  FULL OUTER JOIN table3 ON (table2.uid = tabletable3.uid AND xuid = yuid)
  JOIN table1 USING (uid)

Or if you find it clearer

SELECT *
  FROM table1
  JOIN (table2 FULL OUTER JOIN table 3 ON (table2.uid = tabletable3.uid AND xuid = yuid)) USING (uid)

You might want to have an outer join (RIGHT OUTER JOIN in the first form, LEFT
OUTER JOIN in the second) if you want to list records from table1 for which
there are no matching records in table2 or table3.

This is going to be a hard query to get to be fast though.

The other alternative would be to do a self-join of table1 to table1 and then
use subqueries to check for matching table2 or table3 entries. This would be a
lose if the relationships are relatively sparse, but if you have more tables
it might end up being a win, I don't know.

Something like

SELECT table1.*,
       (select xuid from table2 where uid = child.uid) as xuid,
       (select yuid from table3 where uid = child.uid) as yuid,
       (select zuid from table4 where uid = child.uid) as zuid,
       ...
  FROM table1 
 CROSS JOIN table1 AS child


-- 
greg




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question
@ 2004-11-08 09:38  Uwe C. Schroeder <uwe@oss4u.com>
  parent: Uwe C. Schroeder <uwe@oss4u.com>
  3 siblings, 0 replies; 71+ messages in thread

From: Uwe C. Schroeder @ 2004-11-08 09:38 UTC (permalink / raw)
  To: pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thanks for your answers Greg & Vincent.
Although I solved the problem by a change of schema - I'm happy that I have 
something to digest I didn't know before.  One never learns enough ...

U.C. 

On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder wrote:
> Here's a question for the SQL guru's out there, which I've been trying to
> solve for the last couple of hours. There's got to be a solution to this,
> but somehow I can't find it.
>
> Tables:
>
> table1 (
> 	uid int PK,
>         uname varchar(64)
> )
>
> table2 (
> 	uid int FK to table1,
>         xuid int FK to table 1
> )
>
> table3 (
>   	uid int FK to table1,
> 	yuid int FK to table1
> )
>
> There might be more tables of the type like table2 and table3, but I'd
> already be happy to solve the puzzle with the 3 tables above.
> Ok, assume table1 is the master table - in my case a table used for login
> authentication (some columns removed above)
> table2 and table3 are tables where the uid always references to the uid in
> table1. The second "uid" (xuid and yuid in this example) references to
> another uid record in table1. The problem is that there may or may not be
> entries in table2 (or table3) referencing a specific uid in their second
> uid field.
> Maybe some data:
>
> table1:
> 1 test1
> 2 test2
> 3 test3
>
> table2:
> 1 2
> 1 3
> 3 1
>
> table3:
> 1 2
> 2 3
> 3 2
>
> What I want to do in a view is the following resultset:
>
> uid  uname xuid yuid
>  1    test1     2      2
>  1    test1     3
>  2    test2             3
>  3    test3      1
>  3    test3             2
>
>
> So basically I want to know which uid is connected to which uid, one
> relationship per row. So xuid and yuid shall be identical if records exist
> in both table2 and table3 or the value shall be NULL if a corresponding
> record can't be found in either table2 or table3.
>
> Can anyone here help me out?
>
> Thanks a lot
>
> 	UC

- --
Open Source Solutions 4U, LLC	2570 Fleetwood Drive
Phone:  +1 650 872 2425		San Bruno, CA 94066
Cell:   +1 650 302 2405		United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBjz6bjqGXBvRToM4RApNRAJ9tJzn/3DHSYEZPlGSjzU0H/FsQIwCffw4N
XJuHiF0al0pzInvOb3BP1Jg=
=490X
-----END PGP SIGNATURE-----




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* sql question
@ 2005-03-11 12:26  Steven Verhoeven <Steven2@dmbr.ugent.be>
  0 siblings, 1 reply; 71+ messages in thread

From: Steven Verhoeven @ 2005-03-11 12:26 UTC (permalink / raw)
  To: pgsql-general

My table definition :

   id | fref      | mref
------+-----------+----------
  1   |     23    |   25
  2   |     24    |   28
  3   |     25    |   31
  4   |     26    |   34


i need a query that results in this :

   id |    ref
------+----------
  1   |     23  
  1   |     25
  2   |     24
  2   |     28
  3   |     25   
  3   |     31
  4   |     26   
  4   |     34


Do I need a crosstab-query ?
Who can help me ?




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* sql question
@ 2005-03-11 12:36  Steven Verhoeven <Steven.Verhoeven@dmbr.UGent.be>
  0 siblings, 4 replies; 71+ messages in thread

From: Steven Verhoeven @ 2005-03-11 12:36 UTC (permalink / raw)
  To: pgsql-general; pgsql-novice@postgresql.org

Hi all

My table definition :

   id | fref      | mref
------+-----------+----------
  1   |     23    |   25
  2   |     24    |   28
  3   |     25    |   31
  4   |     26    |   34

My problem :
i need a query that results in this :

   id |    ref
------+----------
  1   |     23  
  1   |     25
  2   |     24
  2   |     28
  3   |     25   
  3   |     31
  4   |     26   
  4   |     34


Do I need a crosstab-query ?
Who can help me ?


-- 
/A computer is like an airconditioner. When windows open, it stops 
working ! /

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- -- -- -- -- -- -- -- -- -- -- -- -- --

Steven Verhoeven, ICT Support Engineer


Department for Molecular Biomedical Research (DMBR)
VIB - Ghent University 'Fiers-Schell-Van Montagu' building
Technologiepark 927B - 9052 Ghent (Zwijnaarde)
Belgium
Tel : +32-(0)9-33-13.606
Fax : +32-(0)9-33-13.609 	E-mail : Steven.Verhoeven@dmbr.UGent.be
URL : http://www.dmbr.UGent.be

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2005-03-14 02:34  Vincent Hikida <vhikida@inreach.com>
  parent: Steven Verhoeven <Steven.Verhoeven@dmbr.UGent.be>
  3 siblings, 1 reply; 71+ messages in thread

From: Vincent Hikida @ 2005-03-14 02:34 UTC (permalink / raw)
  To: Steven Verhoeven <Steven.Verhoeven@dmbr.UGent.be>; pgsql-general; pgsql-novice@postgresql.org

SELECT  t1.id
              ,  t1.fref
    FROM t1
UNION ALL
SELECT t2.id
              , t2.mref
    FROM t2

  ----- Original Message ----- 
  From: Steven Verhoeven 
  To: pgsql-general@postgresql.org ; pgsql-novice@postgresql.org 
  Sent: Friday, March 11, 2005 4:36 AM
  Subject: [GENERAL] sql question


  Hi all 

  My table definition :

     id | fref      | mref
  ------+-----------+----------
    1   |     23    |   25
    2   |     24    |   28
    3   |     25    |   31
    4   |     26    |   34

  My problem : 
  i need a query that results in this :

     id |    ref
  ------+----------
    1   |     23   
    1   |     25
    2   |     24
    2   |     28
    3   |     25    
    3   |     31
    4   |     26    
    4   |     34


  Do I need a crosstab-query ?
  Who can help me ?



  -- 
  A computer is like an airconditioner. When windows open, it stops working !  
  -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 


  Steven Verhoeven, ICT Support Engineer 



        Department for Molecular Biomedical Research (DMBR)
        VIB - Ghent University 'Fiers-Schell-Van Montagu' building
        Technologiepark 927B - 9052 Ghent (Zwijnaarde)
        Belgium 
        Tel : +32-(0)9-33-13.606 
        Fax : +32-(0)9-33-13.609 E-mail : Steven.Verhoeven@dmbr.UGent.be 
        URL : http://www.dmbr.UGent.be 

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2005-03-14 02:39  Russell Smith <mr-russ@pws.com.au>
  parent: Steven Verhoeven <Steven.Verhoeven@dmbr.UGent.be>
  3 siblings, 0 replies; 71+ messages in thread

From: Russell Smith @ 2005-03-14 02:39 UTC (permalink / raw)
  To: Steven Verhoeven <Steven.Verhoeven@dmbr.ugent.be>; +Cc: pgsql-general; pgsql-novice@postgresql.org

On Fri, 11 Mar 2005 11:36 pm, Steven Verhoeven wrote:
> Hi all
> 
> My table definition :
> 
>    id | fref      | mref
> ------+-----------+----------
>   1   |     23    |   25
>   2   |     24    |   28
>   3   |     25    |   31
>   4   |     26    |   34
> 
> My problem :
> i need a query that results in this :
> 
>    id |    ref
> ------+----------
>   1   |     23  
>   1   |     25
>   2   |     24
>   2   |     28
>   3   |     25   
>   3   |     31
>   4   |     26   
>   4   |     34
> 
SELECT id, fref as ref FROM table
UNION ALL
SELECT id, mref as ref FROM table;

Should do the trick.

> 
> Do I need a crosstab-query ?
> Who can help me ?
> 
> 



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: [NOVICE] sql question
@ 2005-03-14 02:42  Vincent Hikida <vhikida@inreach.com>
  parent: Vincent Hikida <vhikida@inreach.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Vincent Hikida @ 2005-03-14 02:42 UTC (permalink / raw)
  To: Vincent Hikida <vhikida@inreach.com>; Steven Verhoeven <Steven.Verhoeven@dmbr.UGent.be>; pgsql-general; pgsql-novice@postgresql.org

OOPs.

I mean

SELECT  t1.id
              ,  t1.fref
    FROM t1
UNION ALL
SELECT t1.id
              , t1.mref
    FROM t1
  ----- Original Message ----- 
  From: Vincent Hikida 
  To: Steven Verhoeven ; pgsql-general@postgresql.org ; pgsql-novice@postgresql.org 
  Sent: Sunday, March 13, 2005 6:34 PM
  Subject: Re: [NOVICE] [GENERAL] sql question


  SELECT  t1.id
                ,  t1.fref
      FROM t1
  UNION ALL
  SELECT t2.id
                , t2.mref
      FROM t2

    ----- Original Message ----- 
    From: Steven Verhoeven 
    To: pgsql-general@postgresql.org ; pgsql-novice@postgresql.org 
    Sent: Friday, March 11, 2005 4:36 AM
    Subject: [GENERAL] sql question


    Hi all 

    My table definition :

       id | fref      | mref
    ------+-----------+----------
      1   |     23    |   25
      2   |     24    |   28
      3   |     25    |   31
      4   |     26    |   34

    My problem : 
    i need a query that results in this :

       id |    ref
    ------+----------
      1   |     23   
      1   |     25
      2   |     24
      2   |     28
      3   |     25    
      3   |     31
      4   |     26    
      4   |     34


    Do I need a crosstab-query ?
    Who can help me ?



    -- 
    A computer is like an airconditioner. When windows open, it stops working !  
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 


    Steven Verhoeven, ICT Support Engineer 



          Department for Molecular Biomedical Research (DMBR)
          VIB - Ghent University 'Fiers-Schell-Van Montagu' building
          Technologiepark 927B - 9052 Ghent (Zwijnaarde)
          Belgium 
          Tel : +32-(0)9-33-13.606 
          Fax : +32-(0)9-33-13.609 E-mail : Steven.Verhoeven@dmbr.UGent.be 
          URL : http://www.dmbr.UGent.be 

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2005-03-14 02:44  Klint Gore <kg@kgb.une.edu.au>
  parent: Steven Verhoeven <Steven.Verhoeven@dmbr.UGent.be>
  3 siblings, 0 replies; 71+ messages in thread

From: Klint Gore @ 2005-03-14 02:44 UTC (permalink / raw)
  To: Steven Verhoeven <Steven.Verhoeven@dmbr.UGent.be>; +Cc: pgsql-general; pgsql-novice@postgresql.org

On Fri, 11 Mar 2005 13:36:17 +0100, Steven Verhoeven
<Steven.Verhoeven@dmbr.UGent.be> wrote:
> Hi all
> 
> My table definition :
> 
>    id | fref      | mref
> ------+-----------+----------
>   1   |     23    |   25
>   2   |     24    |   28
>   3   |     25    |   31
>   4   |     26    |   34
> 
> My problem :
> i need a query that results in this :
> 
>    id |    ref
> ------+----------
>   1   |     23  
>   1   |     25
>   2   |     24
>   2   |     28
>   3   |     25   
>   3   |     31
>   4   |     26   
>   4   |     34
> 
> 
> Do I need a crosstab-query ?

select id, fref from mytable
union all
select id, mref from mytable
order by 1,2

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2005-03-14 05:13  George Essig <george.essig@gmail.com>
  parent: Steven Verhoeven <Steven2@dmbr.ugent.be>
  0 siblings, 1 reply; 71+ messages in thread

From: George Essig @ 2005-03-14 05:13 UTC (permalink / raw)
  To: Steven Verhoeven <Steven2@dmbr.ugent.be>; +Cc: pgsql-general

On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven
<Steven2@dmbr.ugent.be> wrote:
> My table definition :
> 
>    id | fref      | mref
> ------+-----------+----------
>   1   |     23    |   25
>   2   |     24    |   28
>   3   |     25    |   31
>   4   |     26    |   34
> 
> i need a query that results in this :
> 
>    id |    ref
> ------+----------
>   1   |     23
>   1   |     25
>   2   |     24
>   2   |     28
>   3   |     25
>   3   |     31
>   4   |     26
>   4   |     34
> 
> Do I need a crosstab-query ?
> Who can help me ?

select id, fref as ref from my_table
union
select id, mref as ref from my_table;



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2005-03-14 08:41  Ragnar Hafstað <gnari@simnet.is>
  parent: George Essig <george.essig@gmail.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Ragnar Hafstað @ 2005-03-14 08:41 UTC (permalink / raw)
  To: George Essig <george.essig@gmail.com>; +Cc: Steven Verhoeven <Steven2@dmbr.ugent.be>; pgsql-general

On Sun, 2005-03-13 at 23:13 -0600, George Essig wrote:
> On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven
> <Steven2@dmbr.ugent.be> wrote:

> [snip problem]

> select id, fref as ref from my_table
> union
> select id, mref as ref from my_table;

union ALL

(see other replies)

gnari





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: sql question
@ 2005-03-14 17:50  Chris Travers <chris@travelamericas.com>
  parent: Steven Verhoeven <Steven.Verhoeven@dmbr.UGent.be>
  3 siblings, 0 replies; 71+ messages in thread

From: Chris Travers @ 2005-03-14 17:50 UTC (permalink / raw)
  To: Steven Verhoeven <Steven.Verhoeven@dmbr.UGent.be>; pgsql-general

Steven Verhoeven wrote:

> Hi all
>
> My table definition :
>
>    id | fref      | mref
> ------+-----------+----------
>   1   |     23    |   25
>   2   |     24    |   28
>   3   |     25    |   31
>   4   |     26    |   34
>
> My problem :
> i need a query that results in this :
>
>    id |    ref
> ------+----------
>   1   |     23  
>   1   |     25
>   2   |     24
>   2   |     28
>   3   |     25   
>   3   |     31
>   4   |     26   
>   4   |     34
>
>
> Do I need a crosstab-query ?
> Who can help me ?


How about
select id, mref AS ref from table
UNION
select id, fref AS ref from table

Or is that not what you want?

Best Wishes,
Chris Travers
Metatron Technology Consulting

>
>
> -- 
> /A computer is like an airconditioner. When windows open, it stops 
> working ! /
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>
> Steven Verhoeven, ICT Support Engineer
>
>
> Department for Molecular Biomedical Research (DMBR)
> VIB - Ghent University 'Fiers-Schell-Van Montagu' building
> Technologiepark 927B - 9052 Ghent (Zwijnaarde)
> Belgium
> Tel : +32-(0)9-33-13.606
> Fax : +32-(0)9-33-13.609 	E-mail : Steven.Verhoeven@dmbr.UGent.be
> URL : http://www.dmbr.UGent.be
>
>
>




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL Question
@ 2005-04-15 11:58  Alex <alex@meerkatsoft.com>
  0 siblings, 3 replies; 71+ messages in thread

From: Alex @ 2005-04-15 11:58 UTC (permalink / raw)
  To: pgsql-general

Hi,

i have a table

ProdId | LastUpdate
-------+------------
100    | 2005-04-01
100    | 2005-03-01
100    | 2005-02-01
200    | 2005-04-01
200    | 2005-03-01
200    | 2005-02-01

- How can i select only the newest record for each ProdId ?

100    | 2005-04-01
200    | 2005-04-01

- How can i select to retrieve the last 2 dates in record

100    | 2005-04-01 | 2005-03-01
200    | 2005-04-01 | 2005-03-01


Thanks
Alex





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2005-04-15 12:15  Thomas Kellerer <spam_eater@gmx.net>
  parent: Alex <alex@meerkatsoft.com>
  2 siblings, 0 replies; 71+ messages in thread

From: Thomas Kellerer @ 2005-04-15 12:15 UTC (permalink / raw)
  To: pgsql-general

On 15.04.2005 13:58 Alex wrote:

> Hi,
> 
> i have a table
> 
> ProdId | LastUpdate
> -------+------------
> 100    | 2005-04-01
> 100    | 2005-03-01
> 100    | 2005-02-01
> 200    | 2005-04-01
> 200    | 2005-03-01
> 200    | 2005-02-01
> 
> - How can i select only the newest record for each ProdId ?
> 
> 100    | 2005-04-01
> 200    | 2005-04-01
> 
SELECT prodid,
       lastupdate
FROM produpdate p1
WHERE lastupdate = (SELECT MAX (lastupdate) FROM produpdate p2 WHERE p2.prodid =
p1.prodid)

Thomas




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2005-04-15 12:23  GIROIRE, Nicolas (COFRAMI) <nicolas.giroire@airbus.com>
  0 siblings, 1 reply; 71+ messages in thread

From: GIROIRE, Nicolas (COFRAMI) @ 2005-04-15 12:23 UTC (permalink / raw)
  To: Alex <alex@meerkatsoft.com>; pgsql-general

For the first request (How can i select only the newest record for each ProdId ?), you can do :

select * from test."tableProd" u
	where u."LastUpdate" = (select max(t."LastUpdate") 
					from test."tableProd" t
					where u."ProdId" = t."ProdId")





-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Alex
Envoyé : vendredi 15 avril 2005 13:59
À : pgsql-general@postgresql.org
Objet : [GENERAL] SQL Question


Hi,

i have a table

ProdId | LastUpdate
-------+------------
100    | 2005-04-01
100    | 2005-03-01
100    | 2005-02-01
200    | 2005-04-01
200    | 2005-03-01
200    | 2005-02-01

- How can i select only the newest record for each ProdId ?

100    | 2005-04-01
200    | 2005-04-01

- How can i select to retrieve the last 2 dates in record

100    | 2005-04-01 | 2005-03-01
200    | 2005-04-01 | 2005-03-01


Thanks
Alex



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

This mail has originated outside your organization,
either from an external partner or the Global Internet. 
Keep this in mind if you answer this message.

This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2005-04-15 13:41  Richard Huxton <dev@archonet.com>
  parent: GIROIRE, Nicolas (COFRAMI) <nicolas.giroire@airbus.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Richard Huxton @ 2005-04-15 13:41 UTC (permalink / raw)
  To: GIROIRE, Nicolas (COFRAMI) <nicolas.giroire@airbus.com>; +Cc: Alex <alex@meerkatsoft.com>; pgsql-general

GIROIRE, Nicolas (COFRAMI) wrote:
> For the first request (How can i select only the newest record for each ProdId ?), you can do :
> 
> select * from test."tableProd" u
> 	where u."LastUpdate" = (select max(t."LastUpdate") 
> 					from test."tableProd" t
> 					where u."ProdId" = t."ProdId")

Although this only guarantees one row if LastUpdate is unique for every 
ProdId.
-- 
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2005-04-15 13:56  GIROIRE, Nicolas (COFRAMI) <nicolas.giroire@airbus.com>
  0 siblings, 0 replies; 71+ messages in thread

From: GIROIRE, Nicolas (COFRAMI) @ 2005-04-15 13:56 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: Alex <alex@meerkatsoft.com>; pgsql-general

Ok but you haven't specify that and in your example there is no similar use

one solution is to change LastUpdate type and use timestamp.

to insert you make : 
insert into test."tableProd" values (100, '2004-05-01 02:52:12') but it exists other format for time....see postgresql doc for different type (here the french version http://traduc.postgresqlfr.org/pgsql-fr/datatype-datetime.html)

With timestamp the accuracy is better, you have until second.




-----Message d'origine-----
De : Richard Huxton [mailto:dev@archonet.com]
Envoyé : vendredi 15 avril 2005 15:42
À : GIROIRE, Nicolas (COFRAMI)
Cc : Alex; pgsql-general@postgresql.org
Objet : Re: [GENERAL] SQL Question


GIROIRE, Nicolas (COFRAMI) wrote:
> For the first request (How can i select only the newest record for each ProdId ?), you can do :
> 
> select * from test."tableProd" u
> 	where u."LastUpdate" = (select max(t."LastUpdate") 
> 					from test."tableProd" t
> 					where u."ProdId" = t."ProdId")

Although this only guarantees one row if LastUpdate is unique for every 
ProdId.
-- 
   Richard Huxton
   Archonet Ltd

This mail has originated outside your organization,
either from an external partner or the Global Internet. 
Keep this in mind if you answer this message.

This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2005-04-15 14:42  jday@gisolutions.us
  0 siblings, 1 reply; 71+ messages in thread

From: jday@gisolutions.us @ 2005-04-15 14:42 UTC (permalink / raw)
  To: Thomas Kellerer <spam_eater@gmx.net>; pgsql-general

select max(lastupdate),prodid
from tablename
group by prodid


-----Original Message-----

From:  Thomas Kellerer <spam_eater@gmx.net>
Subj:  Re: [GENERAL] SQL Question
Date:  Fri Apr 15, 2005 7:24 am
Size:  621 bytes
To:  pgsql-general@postgresql.org

On 15.04.2005 13:58 Alex wrote:

> Hi,
> 
> i have a table
> 
> ProdId | LastUpdate
> -------+------------
> 100    | 2005-04-01
> 100    | 2005-03-01
> 100    | 2005-02-01
> 200    | 2005-04-01
> 200    | 2005-03-01
> 200    | 2005-02-01
> 
> - How can i select only the newest record for each ProdId ?
> 
> 100    | 2005-04-01
> 200    | 2005-04-01
> 
SELECT prodid,
       lastupdate
FROM produpdate p1
WHERE lastupdate = (SELECT MAX (lastupdate) FROM produpdate p2 WHERE p2.prodid =
p1.prodid)

Thomas


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




Joseph M Day
Global Innovative Solutions
651 W Washington Blvd
Chicago, IL 60661
D: (312) 371-3054
F: (312) 421-8557



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2005-04-15 15:47  Thomas Kellerer <spam_eater@gmx.net>
  parent: jday@gisolutions.us
  0 siblings, 0 replies; 71+ messages in thread

From: Thomas Kellerer @ 2005-04-15 15:47 UTC (permalink / raw)
  To: pgsql-general

jday@gisolutions.us wrote on 15.04.2005 16:42:

> select max(lastupdate),prodid
> from tablename
> group by prodid
> 

Even better :)

Thomas




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2005-04-15 23:05  Bruno Wolff III <bruno@wolff.to>
  parent: Alex <alex@meerkatsoft.com>
  2 siblings, 0 replies; 71+ messages in thread

From: Bruno Wolff III @ 2005-04-15 23:05 UTC (permalink / raw)
  To: Alex <alex@meerkatsoft.com>; +Cc: pgsql-general

On Fri, Apr 15, 2005 at 21:58:31 +1000,
  Alex <alex@meerkatsoft.com> wrote:
> Hi,
> 
> i have a table
> 
> ProdId | LastUpdate
> -------+------------
> 100    | 2005-04-01
> 100    | 2005-03-01
> 100    | 2005-02-01
> 200    | 2005-04-01
> 200    | 2005-03-01
> 200    | 2005-02-01
> 
> - How can i select only the newest record for each ProdId ?
> 
> 100    | 2005-04-01
> 200    | 2005-04-01

You can also use the Postgres specific DISTINCT ON clause.

SELECT DISTINCT ON (prodid) prodid, lastupdate ORDER BY prodid, lastupdate DESC;



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2005-04-16 08:10  Julian Scarfe <julian@avbrief.com>
  parent: Alex <alex@meerkatsoft.com>
  2 siblings, 1 reply; 71+ messages in thread

From: Julian Scarfe @ 2005-04-16 08:10 UTC (permalink / raw)
  To: Alex <alex@meerkatsoft.com>; pgsql-general

From: "Alex" <alex@meerkatsoft.com>

> - How can i select only the newest record for each ProdId ?
>
> 100    | 2005-04-01
> 200    | 2005-04-01
>

DISTINCT ON was made for this and on the similar tables I have performs 
rather more efficiently than using a subquery.

select distinct on (ProdId) ProdId , LastUpdate
from produpdate
order by ProdId , LastUpdate desc;

> - How can i select to retrieve the last 2 dates in record
>
> 100    | 2005-04-01 | 2005-03-01
> 200    | 2005-04-01 | 2005-03-01

To get the previous one, my first thought is something like:

select distinct on (ProdId) ProdId , LastUpdate
from produpdate p1
where LastUpdate <> (
    select max(LastUpdate ) from produpdate p2 where p2.ProdId = p1.ProdId
)
order by ProdId , LastUpdate desc ;

but there may be a much more efficient way of getting the nth result in 
general.

Julian Scarfe 





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2005-04-17 12:53  Alex <alex@meerkatsoft.com>
  parent: Julian Scarfe <julian@avbrief.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Alex @ 2005-04-17 12:53 UTC (permalink / raw)
  To: Julian Scarfe <julian@avbrief.com>; +Cc: pgsql-general

Julian Scarfe wrote:

> From: "Alex" <alex@meerkatsoft.com>
>
>> - How can i select only the newest record for each ProdId ?
>>
>> 100    | 2005-04-01
>> 200    | 2005-04-01
>>
>
> DISTINCT ON was made for this and on the similar tables I have 
> performs rather more efficiently than using a subquery.
>
> select distinct on (ProdId) ProdId , LastUpdate
> from produpdate
> order by ProdId , LastUpdate desc;
>
>> - How can i select to retrieve the last 2 dates in record
>>
>> 100    | 2005-04-01 | 2005-03-01
>> 200    | 2005-04-01 | 2005-03-01
>
>
> To get the previous one, my first thought is something like:
>
> select distinct on (ProdId) ProdId , LastUpdate
> from produpdate p1
> where LastUpdate <> (
>    select max(LastUpdate ) from produpdate p2 where p2.ProdId = p1.ProdId
> )
> order by ProdId , LastUpdate desc ;
>
> but there may be a much more efficient way of getting the nth result 
> in general.
>
> Julian Scarfe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
>
>
Thanks for the help. will give it a try.

Alex




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL question.
@ 2005-06-01 14:40  FC <lne-1mc8@myamail.com>
  0 siblings, 2 replies; 71+ messages in thread

From: FC @ 2005-06-01 14:40 UTC (permalink / raw)
  To: pgsql-general


Hello SQL Aces !

I want to do a select on a table distinct on linkid and sorted by  
date. I have try this

SELECT DISTINCT ON (linkid) * FROM all_links
   WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',
         TIMESTAMP '2005-06-01') ORDER BY linkid, read_date;

With this request "works" but the raws are sorted as I want. To have  
the rows sorted by date I have done this using a temporary table and  
that works exactly as I want.

CREATE LOCAL TEMP TABLE temp_links WITHOUT OIDS AS
   SELECT DISTINCT ON (linkid) * FROM all_links
   WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',
         TIMESTAMP '2005-06-01') ORDER BY linkid;

SELECT * from temp_links ORDER BY read_date DESC limit 100

My question is... How can I do the same thing in the more efficient  
way and without using a temporary table. Since I am using PHP and the  
table is not deleted at the end of the program because PHP keeps the  
connection to the database open.

Thanks for any suggestions.
Fred




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question.
@ 2005-06-01 15:20  Jim Buttafuoco <jim@contactbda.com>
  parent: FC <lne-1mc8@myamail.com>
  1 sibling, 1 reply; 71+ messages in thread

From: Jim Buttafuoco @ 2005-06-01 15:20 UTC (permalink / raw)
  To: FC <lne-1mc8@myamail.com>; pgsql-general


how about (untested)

select * 
from
(
SELECT DISTINCT ON (linkid) * 
FROM all_links
WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',TIMESTAMP '2005-06-01') 
ORDER BY linkid
) A
ORDER BY read_date DESC limit 100


---------- Original Message -----------
From: FC <lne-1mc8@myamail.com>
To: pgsql-general@postgresql.org
Sent: Wed, 1 Jun 2005 16:40:48 +0200
Subject: [GENERAL] SQL question.

> Hello SQL Aces !
> 
> I want to do a select on a table distinct on linkid and sorted by  
> date. I have try this
> 
> SELECT DISTINCT ON (linkid) * FROM all_links
>    WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',
>          TIMESTAMP '2005-06-01') ORDER BY linkid, read_date;
> 
> With this request "works" but the raws are sorted as I want. To have  
> the rows sorted by date I have done this using a temporary table and  
> that works exactly as I want.
> 
> CREATE LOCAL TEMP TABLE temp_links WITHOUT OIDS AS
>    SELECT DISTINCT ON (linkid) * FROM all_links
>    WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',
>          TIMESTAMP '2005-06-01') ORDER BY linkid;
> 
> SELECT * from temp_links ORDER BY read_date DESC limit 100
> 
> My question is... How can I do the same thing in the more efficient  
> way and without using a temporary table. Since I am using PHP and the  
> table is not deleted at the end of the program because PHP keeps the  
> connection to the database open.
> 
> Thanks for any suggestions.
> Fred
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
------- End of Original Message -------




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question.
@ 2005-06-01 15:32  Martijn van Oosterhout <kleptog@svana.org>
  parent: FC <lne-1mc8@myamail.com>
  1 sibling, 0 replies; 71+ messages in thread

From: Martijn van Oosterhout @ 2005-06-01 15:32 UTC (permalink / raw)
  To: FC <lne-1mc8@myamail.com>; +Cc: pgsql-general

On Wed, Jun 01, 2005 at 04:40:48PM +0200, FC wrote:
> 
> Hello SQL Aces !
> 
> I want to do a select on a table distinct on linkid and sorted by  
> date. I have try this

How about a subquery?:

SELECT * FROM
 ( SELECT DISTINCT ON (linkid) * FROM all_links
   WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',
         TIMESTAMP '2005-06-01') ORDER BY linkid, read_date) AS sub
ORDER BY read_date DESC limit 100;

Hope this helps,

> My question is... How can I do the same thing in the more efficient  
> way and without using a temporary table. Since I am using PHP and the  
> table is not deleted at the end of the program because PHP keeps the  
> connection to the database open.
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question.
@ 2005-06-01 15:49  FC <lne-1mc8@myamail.com>
  parent: Jim Buttafuoco <jim@contactbda.com>
  0 siblings, 0 replies; 71+ messages in thread

From: FC @ 2005-06-01 15:49 UTC (permalink / raw)
  To: jim@contactbda.com; +Cc: pgsql-general


I need to remember to keep things simple...

Works fine, thanks.

Fred


On Jun 1, 2005, at 5:20 PM, Jim Buttafuoco wrote:

> how about (untested)
>
> select *
> from
> (
> SELECT DISTINCT ON (linkid) *
> FROM all_links
> WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC 
> ('day',TIMESTAMP '2005-06-01')
> ORDER BY linkid
> ) A
> ORDER BY read_date DESC limit 100
>
>
> ---------- Original Message -----------
> From: FC <lne-1mc8@myamail.com>
> To: pgsql-general@postgresql.org
> Sent: Wed, 1 Jun 2005 16:40:48 +0200
> Subject: [GENERAL] SQL question.
>
>
>> Hello SQL Aces !
>>
>> I want to do a select on a table distinct on linkid and sorted by
>> date. I have try this
>>
>> SELECT DISTINCT ON (linkid) * FROM all_links
>>    WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',
>>          TIMESTAMP '2005-06-01') ORDER BY linkid, read_date;
>>
>> With this request "works" but the raws are sorted as I want. To have
>> the rows sorted by date I have done this using a temporary table and
>> that works exactly as I want.
>>
>> CREATE LOCAL TEMP TABLE temp_links WITHOUT OIDS AS
>>    SELECT DISTINCT ON (linkid) * FROM all_links
>>    WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',
>>          TIMESTAMP '2005-06-01') ORDER BY linkid;
>>
>> SELECT * from temp_links ORDER BY read_date DESC limit 100
>>
>> My question is... How can I do the same thing in the more efficient
>> way and without using a temporary table. Since I am using PHP and the
>> table is not deleted at the end of the program because PHP keeps the
>> connection to the database open.
>>
>> Thanks for any suggestions.
>> Fred
>>
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>     (send "unregister YourEmailAddressHere" to  
>> majordomo@postgresql.org)
>>
> ------- End of Original Message -------
>
>
>




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL question
@ 2008-03-29 04:59  kevin kempter <kevin@kevinkempterllc.com>
  0 siblings, 1 reply; 71+ messages in thread

From: kevin kempter @ 2008-03-29 04:59 UTC (permalink / raw)
  To: pgsql-general

Hi List;

I have a table that has 3 date columns :

create table xyz (
xyz_id integer,
date1   timestamp,
date2   timestamp,
date3   timestamp
)


I want to select in a query the xyz_id and the max date column for  
each row
something like :
create table temp2 as select xyz_id (max date?) where ...

Is this - the (max date?) part a case scenario or is there a better,  
more efficient method ?

Thanks in advance




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL question
@ 2008-03-29 05:03  Adam Rich <adam.r@sbcglobal.net>
  parent: kevin kempter <kevin@kevinkempterllc.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Adam Rich @ 2008-03-29 05:03 UTC (permalink / raw)
  To: 'kevin kempter' <kevin@kevinkempterllc.com>; pgsql-general

> I have a table that has 3 date columns :
> 
> create table xyz (
> xyz_id integer,
> date1   timestamp,
> date2   timestamp,
> date3   timestamp
> )
> 
> 
> I want to select in a query the xyz_id and the max date column for
> each row
> something like :
> create table temp2 as select xyz_id (max date?) where ...

Is this what you want?

Select xyz_id, greatest(date1,date2,date3) from xyz where...

http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html#AE
N14508






^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL Question
@ 2014-04-01 20:21  Robert DiFalco <robert.difalco@gmail.com>
  0 siblings, 1 reply; 71+ messages in thread

From: Robert DiFalco @ 2014-04-01 20:21 UTC (permalink / raw)
  To: pgsql-general

I have two queries I would like to combine into one.

I have a table that represents a user's contacts. It has fields like "id,
owner_id, user_id". Owner ID cannot be null but user_id can be null. They
are numeric field, the ID is just generated.

I want a query to retrieve all of a user's contacts but add in a field to
know if there is a mutual relationship between the contact owner.

I get all of a user's contacts like this:

     SELECT c.* FROM contacts c WHERE c.owner_id = :id;

I can then get all contacts that have the owner as a user like this:

    SELECT c.* FROM contacts c WHERE EXISTS(
        SELECT 1 FROM contacts c2 WHERE c2.user_id = c.owner_id AND
c2.owner_id = c.user_id)
        AND c.owner_id = 1;

But what I'd like is to have the EXISTS clause of the second query to show
up as a BOOLEAN field in the result set. I don't want it to scope the
results, just tell me for each contact of the owner, do they also have her
as a contact?

I tried this but it didn't work:

    SELECT c.*, EXISTS(SELECT 1 FROM contacts c2 WHERE c2.owner_id =
c1.user_id AND c2.user_id = c1.owner_id)
    WHERE c.owner_id = :owner;

Thanks!

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2014-04-01 20:27  Robert DiFalco <robert.difalco@gmail.com>
  parent: Robert DiFalco <robert.difalco@gmail.com>
  0 siblings, 2 replies; 71+ messages in thread

From: Robert DiFalco @ 2014-04-01 20:27 UTC (permalink / raw)
  To: pgsql-general

Heh, scratch that, the EXISTS query DOES work. Is this the most efficient
way to perform this kind of query? Thanks!


On Tue, Apr 1, 2014 at 1:21 PM, Robert DiFalco <robert.difalco@gmail.com>wrote:

> I have two queries I would like to combine into one.
>
> I have a table that represents a user's contacts. It has fields like "id,
> owner_id, user_id". Owner ID cannot be null but user_id can be null. They
> are numeric field, the ID is just generated.
>
> I want a query to retrieve all of a user's contacts but add in a field to
> know if there is a mutual relationship between the contact owner.
>
> I get all of a user's contacts like this:
>
>      SELECT c.* FROM contacts c WHERE c.owner_id = :id;
>
> I can then get all contacts that have the owner as a user like this:
>
>     SELECT c.* FROM contacts c WHERE EXISTS(
>         SELECT 1 FROM contacts c2 WHERE c2.user_id = c.owner_id AND
> c2.owner_id = c.user_id)
>         AND c.owner_id = 1;
>
> But what I'd like is to have the EXISTS clause of the second query to show
> up as a BOOLEAN field in the result set. I don't want it to scope the
> results, just tell me for each contact of the owner, do they also have her
> as a contact?
>
> I tried this but it didn't work:
>
>     SELECT c.*, EXISTS(SELECT 1 FROM contacts c2 WHERE c2.owner_id =
> c1.user_id AND c2.user_id = c1.owner_id)
>     WHERE c.owner_id = :owner;
>
> Thanks!
>
>

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2014-04-01 20:38  Paul Jungwirth <pj@illuminatedcomputing.com>
  parent: Robert DiFalco <robert.difalco@gmail.com>
  1 sibling, 0 replies; 71+ messages in thread

From: Paul Jungwirth @ 2014-04-01 20:38 UTC (permalink / raw)
  To: Robert DiFalco <robert.difalco@gmail.com>; +Cc: pgsql-general

> Is this the most efficient way to perform this kind of query?

I don't think there is one answer that's always correct, but you could
compare it with a LEFT OUTER JOIN.

There are lots of articles and blog posts about EXISTS vs OUTER JOIN
vs IN, for all the major RDBMSes. Note that not all these options give
identical results.

Paul


On Tue, Apr 1, 2014 at 1:27 PM, Robert DiFalco <robert.difalco@gmail.com> wrote:
> Heh, scratch that, the EXISTS query DOES work. Is this the most efficient
> way to perform this kind of query? Thanks!
>
>
> On Tue, Apr 1, 2014 at 1:21 PM, Robert DiFalco <robert.difalco@gmail.com>
> wrote:
>>
>> I have two queries I would like to combine into one.
>>
>> I have a table that represents a user's contacts. It has fields like "id,
>> owner_id, user_id". Owner ID cannot be null but user_id can be null. They
>> are numeric field, the ID is just generated.
>>
>> I want a query to retrieve all of a user's contacts but add in a field to
>> know if there is a mutual relationship between the contact owner.
>>
>> I get all of a user's contacts like this:
>>
>>      SELECT c.* FROM contacts c WHERE c.owner_id = :id;
>>
>> I can then get all contacts that have the owner as a user like this:
>>
>>     SELECT c.* FROM contacts c WHERE EXISTS(
>>         SELECT 1 FROM contacts c2 WHERE c2.user_id = c.owner_id AND
>> c2.owner_id = c.user_id)
>>         AND c.owner_id = 1;
>>
>> But what I'd like is to have the EXISTS clause of the second query to show
>> up as a BOOLEAN field in the result set. I don't want it to scope the
>> results, just tell me for each contact of the owner, do they also have her
>> as a contact?
>>
>> I tried this but it didn't work:
>>
>>     SELECT c.*, EXISTS(SELECT 1 FROM contacts c2 WHERE c2.owner_id =
>> c1.user_id AND c2.user_id = c1.owner_id)
>>     WHERE c.owner_id = :owner;
>>
>> Thanks!
>>
>



-- 
_________________________________
Pulchritudo splendor veritatis.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: SQL Question
@ 2014-04-01 20:56  John R Pierce <pierce@hogranch.com>
  parent: Robert DiFalco <robert.difalco@gmail.com>
  1 sibling, 0 replies; 71+ messages in thread

From: John R Pierce @ 2014-04-01 20:56 UTC (permalink / raw)
  To: pgsql-general

On 4/1/2014 1:27 PM, Robert DiFalco wrote:
> Heh, scratch that, the EXISTS query DOES work. Is this the most 
> efficient way to perform this kind of query? Thanks!

I would try and express that as a left outer join, and use (c2.owner_id 
IS NOT NULL)  as your boolean field (or something like that)





-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* SQL question
@ 2023-01-14 16:32  hamann.w@t-online.de
  0 siblings, 0 replies; 71+ messages in thread

From: hamann.w@t-online.de @ 2023-01-14 16:32 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org


Hi,

I have atable with a "name" column, and I order it usually
order by regexp_match(name, 'regex1'), regexp_match(name, 'regex2')
orthe equivalent
order by (regexp_match(name, 'regex1'))[1], (regexp_match(name, 'regex2'))[2]

Is there a wayto create a function so the statement could read
select ... order by myfunc(name)

Best regards
Wolfgang








^ permalink  raw  reply  [nested|flat] 71+ messages in thread


end of thread, other threads:[~2023-01-14 16:32 UTC | newest]

Thread overview: 71+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-10 05:45 SQL Question Matthew <matt@ctlno.com>
1999-03-10 06:05 RE: [GENERAL] SQL Question Michael Davis <michael.davis@prevuenet.com>
1999-11-17 19:53 sql question Herbert Liechti <Herbert.Liechti@thinx.ch>
1999-11-18 12:32 Re: [GENERAL] sql question Herbert Liechti <Herbert.Liechti@thinx.ch>
1999-11-18 16:37 Re: sql question Ed Loehr <ELOEHR@austin.rr.com>
1999-11-18 21:09 ` Re: [GENERAL] Re: sql question Herbert Liechti <Herbert.Liechti@thinx.ch>
1999-11-19 00:17   ` Re: [GENERAL] Re: sql question Ed Loehr <ELOEHR@austin.rr.com>
1999-11-19 07:06     ` Re: [GENERAL] Re: sql question Herbert Liechti <Herbert.Liechti@thinx.ch>
2000-01-14 22:16 sql question rut <rutl@earthlink.net>
2000-07-10 10:37 SQL question Travis Bauer <trbauer@indiana.edu>
2000-07-10 12:27 ` Re: SQL question NRonayette <r97m10@socotec.fr>
2000-07-12 22:33 sql question Travis Bauer <trbauer@indiana.edu>
2000-07-12 23:25 ` Re: sql question Ross J. Reedstrom <reedstrm@rice.edu>
2000-07-13 10:18   ` Re: sql question Travis Bauer <trbauer@indiana.edu>
2000-09-27 19:11 sql question chris markiewicz <cmarkiew@commnav.com>
2000-09-27 19:20 Re: sql question Adam Lang <aalang@rutgersinsurance.com>
2000-11-23 15:23 SQL question Igor' Robul' <igor@raduga.dyndns.org>
2001-02-22 21:13 SQL Question Matthew <matt@ctlno.com>
2001-02-22 21:37 ` Mitch Vincent <mitch@venux.net>
2001-03-08 18:36 SQL Question Matthew <matt@ctlno.com>
2001-03-08 20:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-13 18:45 sql question u95886230@spawnkill.ip-mobilphone.net
2001-05-15 10:45 ` Re: sql question Justin Clift <justin@postgresql.org>
2001-05-16 05:26 ` Re: sql question will trillich <will@serensoft.com>
2001-05-16 20:03   ` Re: sql question Joel Burton <jburton@scw.org>
2001-05-21 00:44 ` Re: sql question Blackin <rblackin@home.com>
2001-11-02 01:17 SQL question Johnson, Shaunn <SJohnson6@bcbsm.com>
2002-04-17 16:50 SQL question.. Alexis Maldonado <amaldona@ctcd.cc.tx.us>
2002-04-18 08:05 ` Re: SQL question.. Petr Bravenec <pbravenec@solartec.cz>
2003-06-09 13:30 SQL question Guillaume Houssay <ghoussay@noos.fr>
2003-06-10 05:32 ` Re: SQL question Martijn van Oosterhout <kleptog@svana.org>
2003-06-10 07:04 ` Re: SQL question Richard Huxton <dev@archonet.com>
2003-06-19 16:36 SQL question Együd Csaba <csegyud@freemail.hu>
2003-06-19 18:37 ` Re: SQL question Richard Huxton <dev@archonet.com>
2004-07-20 20:31 SQL  (?) question Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2004-07-20 21:24 ` Re: SQL  (?) question Mike Mascari <mascarm@mascari.com>
2004-11-06 23:13 SQL question Uwe C. Schroeder <uwe@oss4u.com>
2004-11-07 01:17 ` Re: SQL question Uwe C. Schroeder <uwe@oss4u.com>
2004-11-07 05:53 ` Re: SQL question Vincent Hikida <vhikida@inreach.com>
2004-11-07 20:16 ` Re: SQL question Greg Stark <gsstark@mit.edu>
2004-11-08 09:38 ` Re: SQL question Uwe C. Schroeder <uwe@oss4u.com>
2005-03-11 12:26 sql question Steven Verhoeven <Steven2@dmbr.ugent.be>
2005-03-14 05:13 ` Re: sql question George Essig <george.essig@gmail.com>
2005-03-14 08:41   ` Re: sql question Ragnar Hafstað <gnari@simnet.is>
2005-03-11 12:36 sql question Steven Verhoeven <Steven.Verhoeven@dmbr.UGent.be>
2005-03-14 02:34 ` Re: sql question Vincent Hikida <vhikida@inreach.com>
2005-03-14 02:42   ` Re: [NOVICE] sql question Vincent Hikida <vhikida@inreach.com>
2005-03-14 02:39 ` Re: sql question Russell Smith <mr-russ@pws.com.au>
2005-03-14 02:44 ` Re: sql question Klint Gore <kg@kgb.une.edu.au>
2005-03-14 17:50 ` Re: sql question Chris Travers <chris@travelamericas.com>
2005-04-15 11:58 SQL Question Alex <alex@meerkatsoft.com>
2005-04-15 12:15 ` Thomas Kellerer <spam_eater@gmx.net>
2005-04-15 23:05 ` Bruno Wolff III <bruno@wolff.to>
2005-04-16 08:10 ` Julian Scarfe <julian@avbrief.com>
2005-04-17 12:53   ` Alex <alex@meerkatsoft.com>
2005-04-15 12:23 Re: SQL Question GIROIRE, Nicolas (COFRAMI) <nicolas.giroire@airbus.com>
2005-04-15 13:41 ` Richard Huxton <dev@archonet.com>
2005-04-15 13:56 Re: SQL Question GIROIRE, Nicolas (COFRAMI) <nicolas.giroire@airbus.com>
2005-04-15 14:42 Re: SQL Question jday@gisolutions.us
2005-04-15 15:47 ` Thomas Kellerer <spam_eater@gmx.net>
2005-06-01 14:40 SQL question. FC <lne-1mc8@myamail.com>
2005-06-01 15:20 ` Re: SQL question. Jim Buttafuoco <jim@contactbda.com>
2005-06-01 15:49   ` Re: SQL question. FC <lne-1mc8@myamail.com>
2005-06-01 15:32 ` Re: SQL question. Martijn van Oosterhout <kleptog@svana.org>
2008-03-29 04:59 SQL question kevin kempter <kevin@kevinkempterllc.com>
2008-03-29 05:03 ` Re: SQL question Adam Rich <adam.r@sbcglobal.net>
2014-04-01 20:21 SQL Question Robert DiFalco <robert.difalco@gmail.com>
2014-04-01 20:27 ` Robert DiFalco <robert.difalco@gmail.com>
2014-04-01 20:38   ` Paul Jungwirth <pj@illuminatedcomputing.com>
2014-04-01 20:56   ` John R Pierce <pierce@hogranch.com>
2023-01-14 16:32 SQL question hamann.w@t-online.de

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