[GENERAL] SQL Question

[Robert DiFalco]

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!

Re: [GENERAL] SQL Question

[Robert DiFalco]

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.difa...@gmail.comwrote:

 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!

Re: [GENERAL] SQL Question

[Paul Jungwirth]

 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.difa...@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.difa...@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

Re: [GENERAL] SQL Question

[John R Pierce]

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

[GENERAL] SQL question on chunking aggregates

[Owen Hartnett]

Hi all:

I have a table that has multiple records for a single owner_id.  I'm able to 
use array_arg to combine the records into a single row, which works fine.  I'm 
using this sql:

select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue + 
revalbuildingvalues) from parcel group by owner_id;

Which results in the following (sometimes there's only one record per 
aggregate, sometimes multiple):

1030600;{154191};{244690}
1030900;{22202};{217210}
1031130;{113135,113138,113132,113130,113133,113 
   127,113126,113131,113129,113136,113125,113   
 137,113134,113
128};{7700,7700,7700,7700,7700,7700,7700,7700,7700,191770,7700,7700,7700,7700}

What I want to do, is where there are more than 5 rows involved in the 
aggregate, as in the last example, to split it into multiple rows of 5 
aggregated rows.  It's for a mailing list and I want to combine like addresses 
into one record, but if I'm over 5, I have to print the rest on a separate 
letter.

1031130;{113135,113138,113132,113130,113
133};{7700,7700,7700,7700,7700}
1031130;{113127,113126,113131,113129,113
136};{7700, 7700,7700,7700,191770}
1031130;{113125,113137,113134,113
128};{7700,7700,7700,7700}

 It looks like I should be able to use the window function to do this, but I've 
been unsuccessful.  The following runs, but doesn't seem to have any effect:

select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue + 
revalbuildingvalues) from parcel group by owner_id
window mywindow as (rows between current row and 5 following);

Does anyone have any suggestions on what I should try?

-Owen

Re: [GENERAL] SQL question on chunking aggregates

[Merlin Moncure]

On Tue, Mar 4, 2014 at 1:49 PM, Owen Hartnett o...@clipboardinc.com wrote:
 Hi all:

 I have a table that has multiple records for a single owner_id.  I'm able to
 use array_arg to combine the records into a single row, which works fine.
 I'm using this sql:

 select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue +
 revalbuildingvalues) from parcel group by owner_id;

 Which results in the following (sometimes there's only one record per
 aggregate, sometimes multiple):

 1030600;{154191};{244690}
 1030900;{22202};{217210}
 1031130;{113135,113138,113132,113130,113
 133,113127,113126,113131,113129,113136,113
 125,113137,113134,113
 128};{7700,7700,7700,7700,7700,7700,7700,7700,7700,191770,7700,7700,7700,7700}

 What I want to do, is where there are more than 5 rows involved in the
 aggregate, as in the last example, to split it into multiple rows of 5
 aggregated rows.  It's for a mailing list and I want to combine like
 addresses into one record, but if I'm over 5, I have to print the rest on a
 separate letter.

 1031130;{113135,113138,113132,113130,113
 133};{7700,7700,7700,7700,7700}
 1031130;{113127,113126,113131,113129,113
 136};{7700, 7700,7700,7700,191770}
 1031130;{113125,113137,113134,113
 128};{7700,7700,7700,7700}

  It looks like I should be able to use the window function to do this, but
 I've been unsuccessful.  The following runs, but doesn't seem to have any
 effect:

 select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue +
 revalbuildingvalues) from parcel group by owner_id
 window mywindow as (rows between current row and 5 following);

 Does anyone have any suggestions on what I should try?

 -Owen

I didn't test it, but something along the lines of:

select
  owner_id,
  array_agg(maplot),
  array_agg(totalvalues)
from
(
  select
owner_id,
trim(maplot) as maplot,
revallandvalue + revalbuildingvalues as totalvalues,
row_number() over (partition by owner_id) as n
  from parcel
) q
group by owner_id, (n - 1)/5;

merlin


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

Re: [GENERAL] SQL question on chunking aggregates

[David Johnston]

Merlin Moncure-2 wrote
 On Tue, Mar 4, 2014 at 1:49 PM, Owen Hartnett lt;

 owen@

 gt; wrote:
 
  It looks like I should be able to use the window function to do this,
 but
 I've been unsuccessful.  The following runs, but doesn't seem to have any
 effect:

 select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue +
 revalbuildingvalues) from parcel group by owner_id
 window mywindow as (rows between current row and 5 following);

 Does anyone have any suggestions on what I should try?

 -Owen
 
 I didn't test it, but something along the lines of:
 
 select
   owner_id,
   array_agg(maplot),
   array_agg(totalvalues)
 from
 (
   select
 owner_id,
 trim(maplot) as maplot,
 revallandvalue + revalbuildingvalues as totalvalues,
 row_number() over (partition by owner_id) as n
   from parcel
 ) q
 group by owner_id, (n - 1)/5;

Yeah, a window cannot work because it cannot be defined to provide disjoint
subsets.

In most cases multiple invocations of array_agg(...) - at the same level in
a query - will see the same row order but that is not something that it is
wise to rely upon.  Any time you want to have synchronized array_agg(...)
calls you should add identical explicit ORDER BY clauses to them; or better
yet combine that data into a custom datatype and then store that in the
array.

The solution is as Merlin presents; you need to use integer division to
bucket the rows and then call the array_agg(...) using those groups.  I like
to keep the bucket ID around in order to capture the original order but as
shown it is not a requirement.

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/SQL-question-on-chunking-aggregates-tp5794680p5794694.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] SQL question re aggregates joins

[Scott Ribe]

OK, this does not work:

select max(t1.When), t1.Pt_Id, t2.DateOfBirth
from PtStaffAccess t1, Person t2
where t1.Pt_Id = t2.id
group by t1.Pt_Id;

But this does:

select max(t1.When), t1.Pt_Id, min(t2.DateOfBirth)
from PtStaffAccess t1, Person t2
where t1.Pt_Id = t2.id
group by t1.Pt_Id;

Now the error message was clear, and I think PG is following the standard
here. But I have a question just for my own education.

It seems to me, given that Person.id is declared as the primary key, it
should possible to deduce that there is no way that the 1st query could ever
have multiple values of DateOfBirth to choose from when building a result
row. Am I missing something? Or am I right, that this is something that SQL
could do but simply doesn't, for whatever reason, historical, complexity...

In fact, what's even more surprising to me, is that if I change the grouping
to the other side of the join, it still doesn't work:

select max(t1.When), t2.id, t2.DateOfBirth
from PtStaffAccess t1, Person t2
where t1.Pt_Id = t2.id
group by t2.id;

Come on, I'm grouping on the primary key and it thinks that there might be
multiple values for the other columns?

-- 
Scott Ribe
scott_r...@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice



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

Re: [GENERAL] SQL question re aggregates joins

[Thom Brown]

On 28 January 2010 21:32, Scott Ribe scott_r...@killerbytes.com wrote:

 OK, this does not work:

 select max(t1.When), t1.Pt_Id, t2.DateOfBirth
 from PtStaffAccess t1, Person t2
 where t1.Pt_Id = t2.id
 group by t1.Pt_Id;

 But this does:

 select max(t1.When), t1.Pt_Id, min(t2.DateOfBirth)
 from PtStaffAccess t1, Person t2
 where t1.Pt_Id = t2.id
 group by t1.Pt_Id;

 Now the error message was clear, and I think PG is following the standard
 here. But I have a question just for my own education.

 It seems to me, given that Person.id is declared as the primary key, it
 should possible to deduce that there is no way that the 1st query could
 ever
 have multiple values of DateOfBirth to choose from when building a result
 row. Am I missing something? Or am I right, that this is something that SQL
 could do but simply doesn't, for whatever reason, historical, complexity...

 In fact, what's even more surprising to me, is that if I change the
 grouping
 to the other side of the join, it still doesn't work:

 select max(t1.When), t2.id, t2.DateOfBirth
 from PtStaffAccess t1, Person t2
 where t1.Pt_Id = t2.id
 group by t2.id;

 Come on, I'm grouping on the primary key and it thinks that there might be
 multiple values for the other columns?


You can't include an aggregate in the select if you don't group by
non-aggregates, so it should be:

select max(t1.When), t1.Pt_Id, t2.DateOfBirth
from PtStaffAccess t1, Person t2
where t1.Pt_Id = t2.id
group by t1.Pt_Id, t2.DateOfBirth;

and likewise

select max(t1.When), t2.id, t2.DateOfBirth
from PtStaffAccess t1, Person t2
where t1.Pt_Id = t2.id
group by t2.id, t2.DateOfBirth;

PostgreSQL might already know that there is only 1 date of birth per ID, but
it still doesn't make sense to select them both and group by one of them
when an aggregate is present (even though MySQL will ignore that and just
fill out what it thinks you missed internally)

Regards

Thom

Re: [GENERAL] SQL question re aggregates joins

[Scott Ribe]

 You can't include an aggregate in the select if you don't group by
 non-aggregates, so it should be:
 
 select max(t1.When), t1.Pt_Id, t2.DateOfBirth
 from PtStaffAccess t1, Person t2
 where t1.Pt_Id = t2.id
 group by t1.Pt_Id, t2.DateOfBirth;

I was aware that I could alternatively group by all the columns, but that
actually just highlights the redundancy even more--consider your second
example:

select max(t1.When), t2.id , t2.DateOfBirth
from PtStaffAccess t1, Person t2
where t1.Pt_Id = t2.id
group by t2.id, t2.DateOfBirth;

Given that t2.id is the primary key, grouping by any other column of t2 is
really redundant. I know *what* SQL won't allow me to do, I'm interested in
knowing if there's some reason *why* other than historical...

-- 
Scott Ribe
scott_r...@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice



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

Re: [GENERAL] SQL question re aggregates joins

[Dann Corbit]

 -Original Message-
 From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
 ow...@postgresql.org] On Behalf Of Scott Ribe
 Sent: Thursday, January 28, 2010 2:10 PM
 To: Thom Brown
 Cc: pgsql-general
 Subject: Re: [GENERAL] SQL question re aggregates  joins
 
  You can't include an aggregate in the select if you don't group by
  non-aggregates, so it should be:
 
  select max(t1.When), t1.Pt_Id, t2.DateOfBirth
  from PtStaffAccess t1, Person t2
  where t1.Pt_Id = t2.id
  group by t1.Pt_Id, t2.DateOfBirth;
 
 I was aware that I could alternatively group by all the columns, but
 that
 actually just highlights the redundancy even more--consider your second
 example:
 
 select max(t1.When), t2.id , t2.DateOfBirth
 from PtStaffAccess t1, Person t2
 where t1.Pt_Id = t2.id
 group by t2.id, t2.DateOfBirth;
 
 Given that t2.id is the primary key, grouping by any other column of t2
 is
 really redundant. I know *what* SQL won't allow me to do, I'm
 interested in
 knowing if there's some reason *why* other than historical...

Having a group by on a primary key in a single table is of no value.  After 
all, the group by accomplishes nothing at all.

Having a group by on the primary key of one table in a join does not guarantee 
uniqueness of the join result.

Consider the simplified schema
Table Customers:
Custname varchar(80)
Custaddr varchar(80)
Custid int primary key
Data:
{'Joe', 'Paris France', 1}
{'Fred', 'Seattle USA', 2}

Table Orders:
OrderID int
Custid int
OrderItem int
OrderQty int
OrderPrice Numeric 12.2
Primary key: OrderID, CustID, OrderItem
Foreign key: CustiID references Customers(CustID)
Data:
{1, 1, 17, 2, 12.92},
{1, 1, 12, 5, 14.75},
{2, 2, 9, 1, 27.45},
{2, 2, 2, 8, 11.23},
{2, 2, 1, 1, 123.45}

SELECT c.Custid, c.CustName, c.Custaddr, o.OrderID, o.OrderItem, sum(o.OrderQty 
* o.OrderPrice) as dollars
From Customers c, Orders o 
WHERE c.Custid = o.Custid
GROUP BY c.Custid

Now, c.Custid is the primary key for Customers and also a foreign key for 
Orders and yet the query is utter nonsense.
Had all of the primary key columns for the second table been included, then the 
group by is superfluous.  Having one or more of the primary key columns left 
out from the child table but added to the group by list changes the meaning and 
value of the result.

I have always thought this way for aggregate queries:
1. Add an aggregate function to each column that you are aggregating in some 
way.
2. Add a 'group by' for every column that is not being aggregated.
Anything else means that the query has not been thought through carefully.

IMO-YMMV


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

Re: [GENERAL] SQL question re aggregates joins

[Tom Lane]

Scott Ribe scott_r...@killerbytes.com writes:
 Given that t2.id is the primary key, grouping by any other column of t2 is
 really redundant. I know *what* SQL won't allow me to do, I'm interested in
 knowing if there's some reason *why* other than historical...

SQL92 says so.  More recent versions of the SQL spec describe allowing
omission of grouping columns when one of them can be proven unique,
but it's complicated and we haven't got round to doing it.

regards, tom lane

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

[GENERAL] SQL Question - Recursion

[ktr73]

Hi -

Was wondering if anyone could help / had some thoughts.  I am building
a model for a client, and right now doing customer attrition
modeling.  Basically, the number of customers in this period is equal
to:

Beg # Customers
+ customers added this period
- attrition
Ending # Customers

Obviously getting the beginning number of customers and # of added has
been easy via SQL.  Also, the above is fairly straightforward in a
spreadsheet program like Excel.  But I can't seem to come to grips
with how to model the attrition line item in SQL, as it's based on
last months ending balance ...  which in turn is based on beg + add -
attr = end for the previous period, and so on.

So it seems to be a recursive function as far as I can tell ... any
idea how to model this via SQL?  I can get running totals for the # of
adds with a correlated subquery, but I can't seem to figure out how to
build the attrition into that running total.

All help is greatly appreciated!  Thanks,

Kevin

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

Re: [GENERAL] SQL Question - Recursion

[Guy Rouillier]

ktr73 wrote:

Hi -

Was wondering if anyone could help / had some thoughts.  I am building
a model for a client, and right now doing customer attrition
modeling.  Basically, the number of customers in this period is equal
to:

Beg # Customers
+ customers added this period
- attrition
Ending # Customers

Obviously getting the beginning number of customers and # of added has
been easy via SQL.  Also, the above is fairly straightforward in a
spreadsheet program like Excel.  But I can't seem to come to grips
with how to model the attrition line item in SQL, as it's based on
last months ending balance ...  which in turn is based on beg + add -
attr = end for the previous period, and so on.

So it seems to be a recursive function as far as I can tell ... any
idea how to model this via SQL?  I can get running totals for the # of
adds with a correlated subquery, but I can't seem to figure out how to
build the attrition into that running total.


Don't understand why you need recursion.  Seems like attrition can be 
calculated.  You say you can get the beginning number of customers.  The 
ending number of customers for month N = beginning number of customers 
for month N+1.  So, if you have the beginning, ending and # customers 
added, then attrition = ending - beginning - #added.


--
Guy Rouillier

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

[GENERAL] SQL question

[kevin kempter]

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


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

Re: [GENERAL] SQL question

[Adam Rich]

 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




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

Re: [GENERAL] SQL question: checking all required items

[Carlos Ortíz]

?
Try
 
Select * from people where person_id in (
  Select person_ID from Items_for_people group by Person_id Having Count(*) 
= (
   Select count(*) from Items Where is_required = true))
 
Or something like that. That's the idea. Probe it and tell us.
(May be the sintaxis it's not correct, but I'm new in postgresql. In sql server 
it's ok)
 



De: [EMAIL PROTECTED] en nombre de Raymond O'Donnell
Enviado el: Vie 10/08/2007 03:07 p.m.
Para: 'PostgreSQL'
Asunto: [GENERAL] SQL question: checking all required items



Hi all,

Given the following tables -

   create table people (
 person_id text primary key,
 person_name text,
 [...etc...]
   );

   create table items (
 item_id text primary key,
 item_name text,
 is_required boolean,
 [...etc...]
   );

   create table items_for_people (
 person_id text,
 item_id text,
 primary key (person_id, item_id),
 foreign key person_id references people(person_id),
 foreign key item_id references items(item_id)
   );


- how can I find those people who don't have _all_ of the items which
are marked required?

In other words, how do I select those rows in people which don't have
a corresponding row in items_for_people for *each* row in items
which has is_required=true?

Many thanks,

Ray.

---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] SQL question: checking all required items

[Raymond O'Donnell]

On 10/08/2007 22:03, Carlos Ortíz wrote:


Select * from people where person_id in (
  Select person_ID from Items_for_people group by Person_id Having Count(*) 
= (
   Select count(*) from Items Where is_required = true))


That seems to work fine! I'd only change having count(*) = ... to 
having count(*) = ... to allow for people having other items in 
addition to the required ones.


Ray.

---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---

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

Re: [GENERAL] SQL question: checking all required items

[Raymond O'Donnell]

On 10/08/2007 21:42, Scott Marlowe wrote:


Show us the query when you're done, I'm sure there are enough folks
who'd like to see your solution.


Here's what I came up with:

  select distinct ip.person_id from items_for_people ip
  where exists (
(
  select item_id from items
  where is_required = true
)
except
(
  select ip2.item_id from items_for_people ip2
  inner join items i on (ip2.item_id = i.item_id)
  where ip2.person_id = ip.person_id
  and i.is_required = true
)
  )

This finds all those who don't have all the required items, whatever 
else they may have.


Comments and improvements are welcome!

Thanks for the help,

Ray.

---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---

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

[GENERAL] SQL question: checking all required items

[Raymond O'Donnell]

Hi all,

Given the following tables -

  create table people (
person_id text primary key,
person_name text,
[...etc...]
  );

  create table items (
item_id text primary key,
item_name text,
is_required boolean,
[...etc...]
  );

  create table items_for_people (
person_id text,
item_id text,
primary key (person_id, item_id),
foreign key person_id references people(person_id),
foreign key item_id references items(item_id)
  );


- how can I find those people who don't have _all_ of the items which 
are marked required?


In other words, how do I select those rows in people which don't have 
a corresponding row in items_for_people for *each* row in items 
which has is_required=true?


Many thanks,

Ray.

---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] SQL question: checking all required items

[Scott Marlowe]

On 8/10/07, Raymond O'Donnell [EMAIL PROTECTED] wrote:
 - how can I find those people who don't have _all_ of the items which
 are marked required?

 In other words, how do I select those rows in people which don't have
 a corresponding row in items_for_people for *each* row in items
 which has is_required=true?

Without writing the exact query you need, I'll give you a couple of
ways to solve the problem of finding things in one set that aren't in
another.

select table1.id from table1 left join table2 on (table1.id=table2.id)
where table2.id is null
OR
select table1.id from table1 where table1.id is not in (select id from table2);

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] SQL question: checking all required items

[Raymond O'Donnell]

On 10/08/2007 21:29, Scott Marlowe wrote:



select table1.id from table1 where table1.id is not in (select id from table2);


Duh! I should have thought of that thanks for that, and apologies 
for the stupidity (blame it on the glass of wine I had with dinner!).


Ray.

---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
---

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

Re: [GENERAL] SQL question: checking all required items

[Scott Marlowe]

On 8/10/07, Raymond O'Donnell [EMAIL PROTECTED] wrote:
 On 10/08/2007 21:29, Scott Marlowe wrote:


  select table1.id from table1 where table1.id is not in (select id from 
  table2);

 Duh! I should have thought of that thanks for that, and apologies
 for the stupidity (blame it on the glass of wine I had with dinner!).

It's only obvious after you've done it a few times...

Show us the query when you're done, I'm sure there are enough folks
who'd like to see your solution.

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

[GENERAL] SQL Question - Group By and % results per row

[Mike]

I have a SQL statement that uses group-by to generate reports as such:


GroupFieldClicks
---
Page 1  50
Page 2  20
Page 3  30

Now, as an added field, I also want it to tell me what percent of
total clicks each row represents.

GroupFieldClicks Percent
---   
Page 1  50   50%
Page 2  20   20%
Page 3  30   30%

How do I get access to the total of all clicks on per row basis so I
can divide it? The only solution that comes to my mind is create a
subquery that does a (select count(*) from... where... ) of the
original grouped by sql statement.

Thank you for your help in advance,
Regards,
Mike


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] SQL Question - Group By and % results per row

[Bruno Wolff III]

On Mon, Mar 12, 2007 at 12:53:11 -0700,
  Mike [EMAIL PROTECTED] wrote:
 
 How do I get access to the total of all clicks on per row basis so I
 can divide it? The only solution that comes to my mind is create a
 subquery that does a (select count(*) from... where... ) of the
 original grouped by sql statement.

That's how you do it.

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

Re: [GENERAL] SQL Question - Using Group By

[Alban Hertroys]

[EMAIL PROTECTED] wrote:
 You could use COUNT() in conjunction with NULLIF:
 
 select Type,
 count(nullif(Active, false)) as Active Count,
 count(nullif(Active, true)) as Inactive Count,
 100 * count(nullif(Active, false)) / count(*) as Active Percent
 from table_name group by Type

Tom Lane suggested me to use sum(Active::int) in a similar situation;
Except that I had boolean expressions instead of values. It is a bit
faster; IMO readability is just different.

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

Re: [GENERAL] SQL Question - Using Group By

[[EMAIL PROTECTED]]

You could use COUNT() in conjunction with NULLIF:

select Type,
count(nullif(Active, false)) as Active Count,
count(nullif(Active, true)) as Inactive Count,
100 * count(nullif(Active, false)) / count(*) as Active Percent
from table_name group by Type


On Feb 23, 2:50 pm, Mike [EMAIL PROTECTED] wrote:
 Hi,

 I have a question about using Group By.

 On a table like this:

 Type (varchar) | Active (boolean)
 
 Type One   |  False
 Type Two   |  True
 Type One   |  True
 Type Fifty   |  Flase
 Type Two   |  True

 Having this table I want a report grouping Types and giving me more
 statistics such as:

 Type|Active Count| Inactive Count|Active
 Percent

 How do i do that?

 I can think of :

 select Type from table_name group by Type

 But that doesn't give me how many active and inactive each had!

 Please help me here understand how to approach this.

 Thank you,
 Mike



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

[GENERAL] SQL Question - Using Group By

[Mike]

Hi,

I have a question about using Group By.

On a table like this:

Type (varchar) | Active (boolean)

Type One   |  False
Type Two   |  True
Type One   |  True
Type Fifty   |  Flase
Type Two   |  True

Having this table I want a report grouping Types and giving me more
statistics such as:

Type|Active Count| Inactive Count|Active
Percent

How do i do that?

I can think of :

select Type from table_name group by Type

But that doesn't give me how many active and inactive each had!

Please help me here understand how to approach this.

Thank you,
Mike


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

   http://archives.postgresql.org/

Re: [GENERAL] SQL Question - Using Group By

[Mike]

Thank you! Exactly what I needed.
Mike

On Feb 23, 4:42 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 You could use COUNT() in conjunction with NULLIF:

 select Type,
 count(nullif(Active, false)) as Active Count,
 count(nullif(Active, true)) as Inactive Count,
 100 * count(nullif(Active, false)) / count(*) as Active Percent
 from table_name group by Type

 On Feb 23, 2:50 pm, Mike [EMAIL PROTECTED] wrote:

  Hi,

  I have a question about using Group By.

  On a table like this:

  Type (varchar) | Active (boolean)
  
  Type One   |  False
  Type Two   |  True
  Type One   |  True
  Type Fifty   |  Flase
  Type Two   |  True

  Having this table I want a report grouping Types and giving me more
  statistics such as:

  Type|Active Count| Inactive Count|Active
  Percent

  How do i do that?

  I can think of :

  select Type from table_name group by Type

  But that doesn't give me how many active and inactive each had!

  Please help me here understand how to approach this.

  Thank you,
  Mike



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

Re: [GENERAL] SQL Question - Using Group By

[Tomas Vondra]

Hi,

I have a question about using Group By.

On a table like this:

Type (varchar) | Active (boolean)

Type One   |  False
Type Two   |  True
Type One   |  True
Type Fifty   |  Flase
Type Two   |  True

Having this table I want a report grouping Types and giving me more
statistics such as:

Type|Active Count| Inactive Count|Active
Percent

How do i do that?

I can think of :

select Type from table_name group by Type
  
This should been quite easy - the trick is aggregate functions omit NULL 
values (maybe there is some other / better way):


SELECT type,
  COUNT(CASE WHEN active THEN 1 ELSE NULL END) AS active_count,
  COUNT(CASE WHEN active THEN NULL ELSE 1 END) AS 
inactive_count,
  COUNT(CASE WHEN active THEN 1 ELSE NULL END) / COUNT(*) 
AS active_pct

FROM table_name;

but have not tested it ;(

Tomas

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] sql question; checks if data already exists before

[Kostas Maistrelis]

nuno wrote:

hi, there. i'm trying to write a SQL statement which does the following
things.

1. checks if data already exists in the database
2. if not, insert data into database otherwise skip.

  

Check this thread :

http://archives.postgresql.org/pgsql-general/2005-10/msg01787.php



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

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

[GENERAL] sql question; checks if data already exists before inserted

[nuno]

hi, there. i'm trying to write a SQL statement which does the following
things.

1. checks if data already exists in the database
2. if not, insert data into database otherwise skip.

for example, i'd like to insert a student called 'Michael Jordan' whose
ID is 'JORDANMICHAEL' only if the id, 'JORDANMICHAEL' does not already
exist in the database. anyway, my query looks like...

insert into student (studentid, fname, lname)
select 'JORDANMICHAEL', 'Michale', 'Jordan' from student where
studentid not in
(select studentid from student);

however, this does not seem to work. it does not insert data even if it
does not exist in the database. hmm!

any clue?

thanks.


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] sql question; checks if data already exists before inserted

[Martijn van Oosterhout]

On Wed, Jun 21, 2006 at 10:59:42PM -0700, nuno wrote:
 insert into student (studentid, fname, lname)
 select 'JORDANMICHAEL', 'Michale', 'Jordan' from student where
 studentid not in
 (select studentid from student);
 
 however, this does not seem to work. it does not insert data even if it
 does not exist in the database. hmm!

There are no NULL studentid's, right? Because that would make NOT IN do
something other than you think. Perhaps NOT EXISTS would work better.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] sql question; checks if data already exists before inserted

[Guy Rouillier]

nuno wrote:
 hi, there. i'm trying to write a SQL statement which does the
 following things. 
 
 1. checks if data already exists in the database
 2. if not, insert data into database otherwise skip.
 
 for example, i'd like to insert a student called 'Michael Jordan'
 whose ID is 'JORDANMICHAEL' only if the id, 'JORDANMICHAEL' does not
 already exist in the database. anyway, my query looks like...  
 
 insert into student (studentid, fname, lname)
 select 'JORDANMICHAEL', 'Michale', 'Jordan' from student where
 studentid not in (select studentid from student); 
 
 however, this does not seem to work. it does not insert data even if
 it does not exist in the database. hmm! 
 
 any clue?

Your query is not doing what you think it's doing.  Try running just the
select portion of the query you provide above (minus the insert into
part.)  You are selecting from student where studentid not in (select
studentid from student).  That will always return the empty set, since
you are looking at the same column and the same table in both the inner
and outer selects.

What you want to do is create a primary key on your student table.  Then
try to do the insert, catching the not unique error return.

-- 
Guy Rouillier


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] [SQL] Question on indexes

[Alvaro Herrera]

Emil Rachovsky wrote:
 
 Hi,
 Can anyone show me a simple way of creating an index
 in PostGre like that:
 create index indName on someTable(someIntColumn DESC)
 ?

Not using that particular syntax, but you can do that if you create the
appropiate operator classes.  Note that if you want to use btree index
on a single column, you don't need to have a descending index, because
btree indexes can be scanned in either direction.  The opclass trick is
only needed if you want to have a multicolumn index.  (I guess in the
other access methods it doesn't make much sense to think of descending
indexes.)

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [GENERAL] [SQL] question

[Matt A.]

The issue has been solved thanks to a custom
nullif_int() function. Which if anyone has the same
issue, it was solved with...


CREATE FUNCTION nullif_int(text) RETURNS integer AS
'SELECT nullif($1,)::int;' LANGUAGE SQL;


SELECTS were not the issue; INSERT INTO a non-text
column was the issue. 

Thanks anyway,
Matthew

--- Thomas F. O'Connell [EMAIL PROTECTED] wrote:

 Matt,
 
 In PostgreSQL 8.0.3, I see:
 
 postgres=# select nullif( '1', '' );
 nullif
 
 1
 (1 row)
 
 postgres=# select nullif( '', '' ) is null;
 ?column?
 --
 t
 (1 row)
 
 What behavior are you expecting?
 
 --
 Thomas F. O'Connell
 Co-Founder, Information Architect
 Sitening, LLC
 
 Strategic Open Source: Open Your i™
 
 http://www.sitening.com/
 110 30th Avenue North, Suite 6
 Nashville, TN 37203-6320
 615-469-5150
 615-469-5151 (fax)
 
 On Aug 24, 2005, at 12:05 AM, Matt A. wrote:
 
  I have a rating section on a website. It has radio
  buttons with values 1-5 according to the rating
 level.
  Lastly there is a null option for n/a. We use null
 for
  n/a so it's excluded from the AVG() calculations.
 
  We used nullif('$value','') on inserts in mssql. 
 We
  moved to postgres and love it but the nullif()
 doesn't
  match empty strings to each other to return null
 other
  than a text type, causing an error. This is a
 major
  part of our application.
 
  AKA nullif('1','') would insert 1 as integer even
  though wrapped in ''. Also nullif('','') would
  evaluate NULL and insert the not a value
  accordingly.
 
  Is there a workaround for this so it doesn't break
 our
  rating system? We cannot always enter a value for
 a
  integer column. Is there any other way to
 accomplish
  this? Please help.
 
 
 
 
  __
  Do you Yahoo!?
  Yahoo! Mail - You care about security. So do we.
  http://promotions.yahoo.com/new_mail
 
  ---(end of  
  broadcast)---
  TIP 2: Don't 'kill -9' the postmaster
 
 
 





Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

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

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

[GENERAL] SQL question.

[FC]

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 [EMAIL PROTECTED])

Re: [GENERAL] SQL question.

[Jim Buttafuoco]

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 [EMAIL PROTECTED]
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 [EMAIL PROTECTED])
--- End of Original Message ---


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

Re: [GENERAL] SQL question.

[Martijn van Oosterhout]

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.


pgp0eOwCUcCBC.pgp
Description: PGP signature

Re: [GENERAL] SQL question.

[FC]

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 [EMAIL PROTECTED]
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  
[EMAIL PROTECTED])



--- End of Original Message ---






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

Re: [GENERAL] SQL Question

[Alex]

Julian Scarfe wrote:
From: Alex [EMAIL PROTECTED]
- 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 [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Thanks for the help. will give it a try.
Alex
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] SQL Question

[Julian Scarfe]

From: Alex [EMAIL PROTECTED]
- 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 [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

[GENERAL] SQL Question

[Alex]

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

Re: [GENERAL] SQL Question

[Thomas Kellerer]

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

Re: [GENERAL] SQL Question

[GIROIRE, Nicolas (COFRAMI)]

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 : [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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.

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

Re: [GENERAL] SQL Question

[Richard Huxton]

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

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

Re: [GENERAL] SQL Question

[GIROIRE, Nicolas (COFRAMI)]

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 timesee 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:[EMAIL PROTECTED]
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.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] SQL Question

[jday]

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


-Original Message-

From:  Thomas Kellerer [EMAIL PROTECTED]
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

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

   http://archives.postgresql.org

Re: [GENERAL] SQL Question

[Thomas Kellerer]

[EMAIL PROTECTED] wrote on 15.04.2005 16:42:
select max(lastupdate),prodid
from tablename
group by prodid
Even better :)
Thomas
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] SQL Question

[Bruno Wolff III]

On Fri, Apr 15, 2005 at 21:58:31 +1000,
  Alex [EMAIL PROTECTED] 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;

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] sql question

[Ragnar Hafstað]

On Sun, 2005-03-13 at 23:13 -0600, George Essig wrote:
 On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven
 [EMAIL PROTECTED] 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



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

   http://archives.postgresql.org

Re: [GENERAL] sql question

[Chris Travers]

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.609E-mail : [EMAIL PROTECTED]
URL : http://www.dmbr.UGent.be


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] sql question

[Steven Verhoeven]

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
: [EMAIL PROTECTED]
  
URL : http://www.dmbr.UGent.be



  

[GENERAL] sql question

[Steven Verhoeven]

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 ?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] sql question

[Vincent Hikida]

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 ; 
  [EMAIL PROTECTED] 
  
  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 | 34My 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 | 34Do 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' 
buildingTechnologiepark 927B - 9052 Ghent 
(Zwijnaarde)Belgium

  Tel : +32-(0)9-33-13.606 Fax : 
+32-(0)9-33-13.609
  E-mail : 
[EMAIL PROTECTED] 
URL : http://www.dmbr.UGent.be

  
  

Re: [GENERAL] sql question

[Russell Smith]

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 ?
 
 

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] sql question

[Klint Gore]

On Fri, 11 Mar 2005 13:36:17 +0100, Steven Verhoeven
[EMAIL PROTECTED] 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   : [EMAIL PROTECTED]   :  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 : :
+---+-+

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [NOVICE] [GENERAL] sql question

[Vincent Hikida]

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 ; 
  [EMAIL PROTECTED] 
  
  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 
; [EMAIL PROTECTED] 

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 | 34My 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 
| 34Do 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' 
  buildingTechnologiepark 927B - 9052 Ghent 
(Zwijnaarde)Belgium
  
Tel : +32-(0)9-33-13.606 Fax : 
  +32-(0)9-33-13.609
E-mail 
  : [EMAIL PROTECTED] 
  URL : http://www.dmbr.UGent.be
  

Re: [GENERAL] sql question

[George Essig]

On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven
[EMAIL PROTECTED] 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;

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

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

Re: [GENERAL] SQL question

[Uwe C. Schroeder]

-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
  1test1 2  2
  1test1 3
  2test2 3
  3test3  1
  3test3 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-


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] SQL question

[Greg Stark]

Uwe C. Schroeder [EMAIL PROTECTED] 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


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] SQL question

[Uwe C. Schroeder]

-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
 1test1 2  2
 1test1 3
 2test2 3
 3test3  1
 3test3 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 [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] SQL question

[Uwe C. Schroeder]

-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
  1test1 2  2
  1test1 3
  2test2 3
  3test3  1
  3test3 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-


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] SQL question

[Vincent Hikida]

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 [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
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
 1test1 2  2
 1test1 3
 2test2 3
 3test3  1
 3test3 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 [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly





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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Andrew Sullivan]

On Mon, Nov 10, 2003 at 08:56:03AM -0800, Scott Chapman wrote:
 I talked with the author or SQLObject about this recently and I thnk 
 he's implementing this correctly, by querying the cursor for the last 
 OID?:

That won't scale unless you index oid.  And your tables will all need
oids, which is not standard any more.

If you do your work in one transaction and get the currval that way,
it is impossible to go wrong.  Also, if you don't return the
connection to the pool before getting the currval, you will not go
wrong.  

A

-- 

Andrew Sullivan 204-4141 Yonge Street
Afilias CanadaToronto, Ontario Canada
[EMAIL PROTECTED]  M2P 2A8
 +1 416 646 3304 x110


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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Greg Stark]

scott.marlowe [EMAIL PROTECTED] writes:

 select tablename.fieldname.currval;

That syntax would be problematic, it would mean to select all rows from
tablename and evaluate fieldname.currval for each one. Actually it's worse, it
would be confused with schemas I think.

The postgres-ish way to do this would be to create a function like currval
that took a table and column and told you the currval of the sequence
associated with it.

Well you can already do something like that:

db= create or replace function currval(text,text) returns bigint as  'select 
currval($1 || ''_'' || $2 || ''_seq'')' language sql strict;
CREATE FUNCTION

db= create table test (a serial);
NOTICE:  CREATE TABLE will create implicit sequence test_a_seq for serial column 
test.a
CREATE TABLE

db= insert into test(a) values (default);
INSERT 14080230 1

db= select currval('test','a');
 currval 
-
   1
(1 row)



The only problem arises if you use table names or column names that cause
postgres to truncate the resulting sequence name. This could be worked-around
by using the dependency information instead of depending on the naming scheme.

But as long as you do that the above works fine. And means you could always
change your naming scheme or method for looking up the associated sequence
later without changing all your sql.

-- 
greg


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

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Doug McNaught]

Scott Chapman [EMAIL PROTECTED] writes:

 On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
  Scott Chapman [EMAIL PROTECTED] writes:
   It would be nice if PostgreSQL could return the primary key it
   inserted with but that may not be a fool-proof solution either.  Is
   there a nice way to handle this situation?
 
  Write a database function that inserts the record and returns the
  primary key value?  That's probably the best way to insulate your app
  from the database structure...
 
 The function still has to know which sequence to pull from doesn't it?

Yes.  It's theoretically possible to derive that information if you
have enough system-tables-fu, but since the function knows which
table it's inserting into, it's not hard to put the proper sequence
name in as well.

 I don't know much about triggers/functions in PG.  Is it possible to 
 have a function that intercepts the information AFTER the sequence 
 value is added as the new primary key and then return it?  This would 
 enable the use of a more generic function.

Sure, in the function you would basically do (I forget the exact
pl/pgsql syntax):

INSERT INTO foo VALUES (...);
SELECT currval('the_pk_sequence') INTO pk;
RETURN pk;

Doesn't remove the need to know or derive the proper sequence name.
There is no what primary key did I just insert built into PG.  And
you will need a separate function for each table.

But this way the DB knowledge resides in the DB and you just have a
nice clean API for inserting data from the clients.  The schema can
change and the API will (homefully) remain the same...

-Doug

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Doug McNaught]

Scott Chapman [EMAIL PROTECTED] writes:

 It would be nice if PostgreSQL could return the primary key it inserted 
 with but that may not be a fool-proof solution either.  Is there a nice 
 way to handle this situation?

Write a database function that inserts the record and returns the
primary key value?  That's probably the best way to insulate your app
from the database structure...

-Doug

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Alvaro Herrera]

On Mon, Nov 10, 2003 at 08:09:29AM -0800, Scott Chapman wrote:

 Chronological events here:
 
 X inserts a new record into A.
 Y inserts a new record into A.
 X fetches currval of the SA. What value does X get in this case, the one 
 from X's insert or Y's?

X's.

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
¿Qué importan los años?  Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo  (Mafalda)

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Scott Chapman]

On Monday 10 November 2003 08:23, David Green wrote:
 Are X  Y two different connections?
 If you execute 2 statements on the same connection and then get
 currval() it will give the last generated id.

 Ex.
 On 1 connection:
 INSERT INTO A (fld) VALUES (val); -- id generated = 1
 INSERT INTO A (fld) VALUES (val2); -- id generated = 2
 SELECT currval('SA');
 2

Thanks for the clarification.  With web applications and connection 
pooling, it would appear that it's quite easy to get incorrect values 
back.  This is what I thought. 

I talked with the author or SQLObject about this recently and I thnk 
he's implementing this correctly, by querying the cursor for the last 
OID?:

def _queryInsertID(self, conn, table, idName, names, values):
c = conn.cursor()
q = self._insertSQL(table, names, values)
if self.debug:
print 'QueryIns: %s' % q
c.execute(q)
c.execute('SELECT %s FROM %s WHERE oid = %s'
  % (idName, table, c.lastoid()))
return c.fetchone()[0]

The other way to do it would be to manually fetch nextval and insert 
into the table over-riding the default for the ID field (assuming it 
defaulted to the nextval in the sequence).  I don't know which way is 
best (for performance, for instance).  

It's be nice if INSERT could be made to return the OID or (better yet) 
the primary key field value when it completes.  That would solve this 
problem in one action and completely remove the need for the second 
query.  I expect it would have to be user-togglable so it didn't break 
with existing code?

Scott

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

   http://archives.postgresql.org

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Kathy Zhu]

I saw this method of Statement class in jdbc.
Will the return int contain the autogenerated key value ??

public int executeUpdate(String sql,
 int autoGeneratedKeys)
  throws SQLException

thanks,
kathy


Scott Chapman wrote:

 On Monday 10 November 2003 08:23, David Green wrote:
  Are X  Y two different connections?
  If you execute 2 statements on the same connection and then get
  currval() it will give the last generated id.
 
  Ex.
  On 1 connection:
  INSERT INTO A (fld) VALUES (val); -- id generated = 1
  INSERT INTO A (fld) VALUES (val2); -- id generated = 2
  SELECT currval('SA');
  2

 Thanks for the clarification.  With web applications and connection
 pooling, it would appear that it's quite easy to get incorrect values
 back.  This is what I thought.

 I talked with the author or SQLObject about this recently and I thnk
 he's implementing this correctly, by querying the cursor for the last
 OID?:

 def _queryInsertID(self, conn, table, idName, names, values):
 c = conn.cursor()
 q = self._insertSQL(table, names, values)
 if self.debug:
 print 'QueryIns: %s' % q
 c.execute(q)
 c.execute('SELECT %s FROM %s WHERE oid = %s'
   % (idName, table, c.lastoid()))
 return c.fetchone()[0]

 The other way to do it would be to manually fetch nextval and insert
 into the table over-riding the default for the ID field (assuming it
 defaulted to the nextval in the sequence).  I don't know which way is
 best (for performance, for instance).

 It's be nice if INSERT could be made to return the OID or (better yet)
 the primary key field value when it completes.  That would solve this
 problem in one action and completely remove the need for the second
 query.  I expect it would have to be user-togglable so it didn't break
 with existing code?

 Scott

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

http://archives.postgresql.org


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Andreas Fromm]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1



Martijn van Oosterhout wrote:
 After you've done the insert on the address table, you can use
 currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have
 to have used nextval() for the original insert.

 Hope this helps,

..going to try it. Thanks

Andreas Fromm

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/rkMwPkvkZVZzNY0RAnajAJ0ePCTi/UODhGAxOs5NuptZAT0tUgCgpNAz
Oqh8rM934O3SRRzv4Mh9S4I=
=E71z
-END PGP SIGNATURE-


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Scott Chapman]

On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
 After you've done the insert on the address table, you can use
 currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you
 have to have used nextval() for the original insert.

What if someone else inserts another address before I get the currval?  
I'm out of luck then, right?



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

   http://archives.postgresql.org

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Doug McNaught]

Scott Chapman [EMAIL PROTECTED] writes:

 On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
  After you've done the insert on the address table, you can use
  currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you
  have to have used nextval() for the original insert.
 
 What if someone else inserts another address before I get the currval?  
 I'm out of luck then, right?

No, currval() handles that--see the docs.

-Doug

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

Re: [GENERAL] SQL-question: returning the id of an insert querry

[Alvaro Herrera]

On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote:
 On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
  After you've done the insert on the address table, you can use
  currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you
  have to have used nextval() for the original insert.
 
 What if someone else inserts another address before I get the currval?  
 I'm out of luck then, right?

No, currval is concurrency-safe.  That's exactly what sequences are for.

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
I call it GNU/Linux. Except the GNU/ is silent. (Ben Reiter)

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

[GENERAL] sql question

[u95886230]

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

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


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

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

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

thanks
[EMAIL PROTECTED]
 



-- 
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

Re: [GENERAL] SQL question - problem with INTERSECT

[hubert depesz lubaczewski]

On Thu, Nov 02, 2000 at 06:50:07AM -0500, Keith L. Musser wrote:
  upper(a.word) = 'JIM' or upper(a.word) = 'JONES'
  upper(s.word) = 'HELLO' or upper(s.word) = 'THERE'
 Interesting possibility.  Unfortunately, the outer select ends up being
 a sequential scan over the entire messages table, where at each row the
 message id is looked up sequentially in the nested query.

did you try my quesry with "upper's" in it?
if so then is your index on a.word or on upper(a.word)?
try making functional indies, and of course vacuum'im tables to make indices
work.
this query *should* work with indices.

depesz

-- 
hubert depesz lubaczewski

 najwspanialsz rzecz jak dao nam nowoczesne spoeczestwo,
  jest niesamowita wrcz atwo unikania kontaktw z nim ...

Re: [GENERAL] SQL question - problem with INTERSECT

[Keith L. Musser]

If I remove the "GROUP BY messages.msgid ...", then the result will be
messages whose subject contains either 'Hello' or 'There' in the
subject, but not necessarily both.

I want messages which have both 'Hello' and 'There' in the subject, and
both 'Jim' and 'Jones' in the author.

(For example, if I needed all of 'Hello', 'There', and 'Now' in the
subject, my first HAVING clause would use a count of 3, while the second
HAVING clause would still use a count of 2, for both 'Jim' and 'Jones'.)
So I cannot remove either having clause without changing the meaning.

What I would really like to know is why INTERSECT does not allow this.
If I understand that, maybe I can figure out how to get what I need.

-Original Message-
From: Igor Roboul [EMAIL PROTECTED]
To: PGSQL-General [EMAIL PROTECTED]
Date: Wednesday, November 01, 2000 12:03 AM
Subject: Re: [GENERAL] SQL question - problem with INTERSECT


On Mon, Oct 30, 2000 at 07:59:06PM -0500, Keith L. Musser wrote:
 "(SELECT messages.msgid FROM messages, subject_index WHERE
 ((subject_index.word='Hello' or subject_index.word='There') and
 (subject_index.msgid = messages.msgid))
 GROUP BY messages.msgid HAVING count(messages.msgid)=2)
 INTERSECT
 (SELECT messages.msgid FROM messages, author_index WHERE
 ((author_index.word='Jim' or author_index.word='Jones') and
 (author_index.msgid = messages.msgid))
 GROUP BY messages.msgid HAVING count(messages.msgid)=2);"
Try removing first "GROUP BY messages.msgid HAVING
count(messages.msgid)=2)"

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

Re: [GENERAL] SQL question - problem with INTERSECT

[Igor Roboul]

On Mon, Oct 30, 2000 at 07:59:06PM -0500, Keith L. Musser wrote:
 "(SELECT messages.msgid FROM messages, subject_index WHERE
 ((subject_index.word='Hello' or subject_index.word='There') and
 (subject_index.msgid = messages.msgid))
 GROUP BY messages.msgid HAVING count(messages.msgid)=2)
 INTERSECT
 (SELECT messages.msgid FROM messages, author_index WHERE
 ((author_index.word='Jim' or author_index.word='Jones') and
 (author_index.msgid = messages.msgid))
 GROUP BY messages.msgid HAVING count(messages.msgid)=2);"
Try removing first "GROUP BY messages.msgid HAVING count(messages.msgid)=2)"

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

Re: [GENERAL] sql question

[Travis Bauer]

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 y1 THEN x ELSE 0 END FROM t1;
 
 From page 33 of Bruce's book, at:

Re: [GENERAL] sql question

[Ross J. Reedstrom]

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 y1 else 0 from t1;

SELECT CASE WHEN y1 THEN x ELSE 0 END FROM t1;

From page 33 of Bruce's book, at:

http://www.postgresql.org/docs/aw_pgsql_book/node52.html

Ross
-- 
Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

[GENERAL] sql question

[Herbert Liechti]

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'),
   ZeitDATETIME  NOT NULL,
   Group_IdINTEGER   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 LiechtiE-Mail: [EMAIL PROTECTED]
ThinX networked business services   Stahlrain 10, CH-5200 Brugg
~~~

RE: [GENERAL] SQL Question

[Michael Davis]

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:[EMAIL PROTECTED]]
Sent:   Tuesday, March 09, 1999 10:46 PM
To: [EMAIL PROTECTED]
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