pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL 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# ¦ Ordertype ¦ Date
1 ¦ A ¦ April
1 ¦ B ¦ May
1 ¦ B ¦ May
2 ¦ B ¦ April
2 ¦ B ¦ May
2 ¦ C ¦ May
3 ¦ C ¦ 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 ¦ TotA ¦ TotB ¦ TotC ¦ Total
1 ¦ 0 ¦ 2 ¦ 0 ¦ 2
2 ¦ 0 ¦ 1 ¦ 1 ¦ 2
3 ¦ 0 ¦ 0 ¦ 1 ¦ 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# ¦ Ordertype ¦ Date
> 1 ¦ A ¦ April
> 1 ¦ B ¦ May
> 1 ¦ B ¦ May
> 2 ¦ B ¦ April
> 2 ¦ B ¦ May
> 2 ¦ C ¦ May
> 3 ¦ C ¦ 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 ¦ TotA ¦ TotB ¦ TotC ¦ Total
> 1 ¦ 0 ¦ 2 ¦ 0 ¦ 2
> 2 ¦ 0 ¦ 1 ¦ 1 ¦ 2
> 3 ¦ 0 ¦ 0 ¦ 1 ¦ 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# ¦ Ordertype ¦ Date
> 1 ¦ A ¦ April
> 1 ¦ B ¦ May
> 1 ¦ B ¦ May
> 2 ¦ B ¦ April
> 2 ¦ B ¦ May
> 2 ¦ C ¦ May
> 3 ¦ C ¦ 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 ¦ TotA ¦ TotB ¦ TotC ¦ Total
> 1 ¦ 0 ¦ 2 ¦ 0 ¦ 2
> 2 ¦ 0 ¦ 1 ¦ 1 ¦ 2
> 3 ¦ 0 ¦ 0 ¦ 1 ¦ 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# ¦ Ordertype ¦ Date
> > 1 ¦ A ¦ April
> > 1 ¦ B ¦ May
> > 1 ¦ B ¦ May
> > 2 ¦ B ¦ April
> > 2 ¦ B ¦ May
> > 2 ¦ C ¦ May
> > 3 ¦ C ¦ 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 ¦ TotA ¦ TotB ¦ TotC ¦ Total
> > 1 ¦ 0 ¦ 2 ¦ 0 ¦ 2
> > 2 ¦ 0 ¦ 1 ¦ 1 ¦ 2
> > 3 ¦ 0 ¦ 0 ¦ 1 ¦ 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# ¦ Ordertype ¦ Date
> 1 ¦ A ¦ April
> 1 ¦ B ¦ May
> 1 ¦ B ¦ May
> 2 ¦ B ¦ April
> 2 ¦ B ¦ May
> 2 ¦ C ¦ May
> 3 ¦ C ¦ 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 ¦ TotA ¦ TotB ¦ TotC ¦ Total
> 1 ¦ 0 ¦ 2 ¦ 0 ¦ 2
> 2 ¦ 0 ¦ 1 ¦ 1 ¦ 2
> 3 ¦ 0 ¦ 0 ¦ 1 ¦ 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