pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfiltering
4+ messages / 4 participants
[nested] [flat]
* filtering
@ 2001-06-27 13:43 sbelow <sbelow@dundee.net>
0 siblings, 1 reply; 4+ messages in thread
From: sbelow @ 2001-06-27 13:43 UTC (permalink / raw)
To: pgsql-sql
I'm using '
m_pSet->Open(AFX_DB_USE_DEFAULT_TYPE,"SELECT * FROM tblPMTasks WHERE
[TaskDue] = 1");
to filter out certain records.
I want to add a filter to the current one how can I go about this? Or should
I reconstruct to a basic 'Open()' statement and use this example given from
MSDN.
CCustSet rsCustSet( NULL );
// Set the filter
rsCustSet.m_strFilter = "state = 'CA'";
// Run the filtered query
rsCustSet.Open( CRecordset::snapshot, "Customers" );
Thanks in advance for your help
Steve
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: filtering
@ 2001-06-28 15:46 Josh Berkus <josh@agliodbs.com>
parent: sbelow <sbelow@dundee.net>
0 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2001-06-28 15:46 UTC (permalink / raw)
To: sbelow <sbelow@dundee.net>; pgsql-sql
Steve,
> I'm using '
> m_pSet->Open(AFX_DB_USE_DEFAULT_TYPE,"SELECT * FROM tblPMTasks WHERE
> [TaskDue] = 1");
> to filter out certain records.
> I want to add a filter to the current one how can I go about this? Or
> should
> I reconstruct to a basic 'Open()' statement and use this example
> given from
> MSDN.
I'm afraid that your question is an ADO question, not a SQL question.
Take a look at Postgresql.org and see if there's a Microsoft <-->
Postgresql list. We can't help you here.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* filtering
@ 2004-12-09 15:25 Kevin B. <db@ke5in.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin B. @ 2004-12-09 15:25 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a 14 million row table with one index on two fields one is a varchar
the other is a date. The combination of the two makes the row unique.
Data
-----------------
name date ... other fields
a 1/1/01
a 1/2/01
a 1/3/01
b 1/1/01
b 1/2/01
d 1/1/01
d 1/2/01
I have a table with just the names. each name occurs once.
UName
---------
name
a
b
c
d
I've tried a number of queries to find which name is in UName but not in
Data. However, they are all taking too long (more than 30 minutes - but
the hard drive is a slow 4200rpm IDE....).
What is the quickest query to get the result that I want? Also, should I
put another index on the Data table for "name" only?
Thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: filtering
@ 2004-12-11 19:01 Bruno Wolff III <bruno@wolff.to>
parent: Kevin B. <db@ke5in.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2004-12-11 19:01 UTC (permalink / raw)
To: Kevin B. <db@ke5in.com>; +Cc: pgsql-sql
On Thu, Dec 09, 2004 at 10:25:25 -0500,
"Kevin B." <db@ke5in.com> wrote:
> Hi,
>
> I have a 14 million row table with one index on two fields one is a varchar
> the other is a date. The combination of the two makes the row unique.
>
> Data
> -----------------
> name date ... other fields
> a 1/1/01
> a 1/2/01
> a 1/3/01
> b 1/1/01
> b 1/2/01
> d 1/1/01
> d 1/2/01
>
> I have a table with just the names. each name occurs once.
> UName
> ---------
> name
> a
> b
> c
> d
>
> I've tried a number of queries to find which name is in UName but not in
> Data. However, they are all taking too long (more than 30 minutes - but
> the hard drive is a slow 4200rpm IDE....).
>
> What is the quickest query to get the result that I want? Also, should I
> put another index on the Data table for "name" only?
It might help if you showed us the explain analyze results from your
attempts.
(All of the suggestions below assuming there aren't any NULL names.)
I think the straight forward way to do this is something like:
SELECT name FROM uname
WHERE NOT EXITS (
SELECT name FROM data WHERE uname.name = data.name
)
;
If you are using 7.4 or later, you might try using NOT IN. (This does
not run efficiently in earlier versions of postgres.)
SELECT name FROM uname WHERE name NOT IN (SELECT name FROM data);
It is also possible to use set subtraction to get the result, but I doubt
this will be faster than using NOT EXISTS. (Using GROUP BY eliminate
duplicates allows the use of a hash aggregate plan if there aren't too
many unique names.)
SELECT name FROM uname
EXCEPT
SELECT name FROM data GROUP BY name
;
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-12-11 19:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-27 13:43 filtering sbelow <sbelow@dundee.net>
2001-06-28 15:46 ` Josh Berkus <josh@agliodbs.com>
2004-12-09 15:25 filtering Kevin B. <db@ke5in.com>
2004-12-11 19:01 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox