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
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
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)
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,
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,
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
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
-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
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
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 #
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
?
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
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(*) =
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
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
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.
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
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
[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
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]
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,
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
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
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
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'
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
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
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
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
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
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)
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
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
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
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
(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
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
[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
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
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
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
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
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
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
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
-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
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)
)
-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
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
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
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
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
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
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
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
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
-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
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
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.
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
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,
eally 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
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)
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
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:
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]
66 matches
Mail list logo
