pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSub select..
3+ messages / 3 participants
[nested] [flat]
* Sub select..
@ 2000-03-31 21:11 Ed <dziablo@lords.com>
0 siblings, 0 replies; 3+ messages in thread
From: Ed @ 2000-03-31 21:11 UTC (permalink / raw)
To: pgsql-sql
Hi,
I would like to know if ther is a way of doing something like this :
select max(col1) from (select count(*) as col1 from table1 where index_field
= 3 group by day);
table1 loking like :
day | index_field | ...
-------------------------------------
1 1
1 1
1 3
1 3
2 3
3 1
3 3
Finally, I only want to know the maximum count(*) value grouped by day
my sub query should return me :
col1
-------
2 (for day 1)
1 (for day 2)
1 (for day 3)
So, Max(col1) would return me " 2 "
but... imbriqued select doesn't seems to work in 6.5.3....
Any suggestions?
/fb
^ permalink raw reply [nested|flat] 3+ messages in thread
* sub SELECT
@ 2002-02-26 20:42 Charles Hauser <chauser@acpub.duke.edu>
2002-03-01 17:42 ` Re: sub SELECT Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Charles Hauser @ 2002-02-26 20:42 UTC (permalink / raw)
To: pgsql-sql
Hi,
I think I should be able to implement a subquerie or subSELECT to
retrieve a set of data from my tables, but have not quite figured it
out. I would appreciate any ideas/suggestions.
Three TABLES involved: clone, clone_contig(relational) and contig.
Example:
clone.(project plate p_row p_column read ver) uniquely defines a
particular clone.
A search using 4 of these 6 columns [clone.(project plate p_row
p_column) = (894 001 A 01)] will return 2 results:
894 001 A 01 x 1
894 001 A 01 y 1
where 'x|y' = clone.read, and '1' = clone.ver.
For each of these two (894001A01x1, 894001A01y1), I want to find the
corresponding contig.
So, a two part query:
1st find ALL clones defined by clone.(project plate p_row p_column)
2nd find ALL contigs related to each clone.
The problem I have had is that the first/internal query returns
multiple values,
$result = $conn->exec(
"SELECT contig.assembly_date,contig.contig_no,contig.ver
FROM clone JOIN clone_contig USING (clone_id)
WHERE clone.clone_id = (
SELECT clone.clone_id
FROM clone
WHERE clone.project = '1024' AND
clone.plate = '001' AND
clone.p_row = 'A' AND
clone.p_column = '01'
)
");
CREATE TABLE "clone" (
"clone_id" int4 DEFAULT nextval('"clone_clone_id_seq"'::text) NOT NULL,
"project" int4 NOT NULL,
"plate" int4 NOT NULL,
"p_row" char(1) NOT NULL,
"p_column" int4 NOT NULL,
"read" char(1) NOT NULL,
"ver" int4 NOT NULL,
"seq" text NOT NULL,
"qual" text NOT NULL,
"length" int4 NOT NULL,
"qual_length" int4 NOT NULL,
"mean_qual_after_trim" int4 NOT NULL,
"qual_start" int4 NOT NULL,
"qual_end" int4 NOT NULL,
"comment" text NOT NULL,
"gb_accessions_id" int4,
CONSTRAINT "clone_pkey" PRIMARY KEY ("clone_id")
);
CREATE UNIQUE INDEX "clone_project_key" ON "clone" ("p_column",
"p_row", "plate", "project", "read", "ver");
CREATE TABLE "clone_contig" (
"clone_id" int4,
"contig_id" int4
);
CREATE UNIQUE INDEX "clone_contig_clone_id_key" ON "clone_contig"
("clone_id", "contig_id");
CREATE TABLE "contig" (
"contig_id" int4 DEFAULT nextval('"contig_contig_id_seq"'::text) NOT NULL,
"assembly_date" date NOT NULL,
"contig_no" int4 NOT NULL,
"ver" int4 NOT NULL,
"length" int4 NOT NULL,
"seq" text NOT NULL,
CONSTRAINT "contig_pkey" PRIMARY KEY ("contig_id")
);
CREATE UNIQUE INDEX "contig_assembly_date_key" ON "contig"
("assembly_date", "contig_no", "ver");
--
Regards,
Chuck
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: sub SELECT
2002-02-26 20:42 sub SELECT Charles Hauser <chauser@acpub.duke.edu>
@ 2002-03-01 17:42 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2002-03-01 17:42 UTC (permalink / raw)
To: Charles Hauser <chauser@acpub.duke.edu>; pgsql-sql
Hey, Chuck!
How's the project going, so far?
> A search using 4 of these 6 columns [clone.(project plate p_row
> p_column) = (894 001 A 01)] will return 2 results:
> 894 001 A 01 x 1
> 894 001 A 01 y 1
> where 'x|y' = clone.read, and '1' = clone.ver.
>
> For each of these two (894001A01x1, 894001A01y1), I want to find the
> corresponding contig.
Why not a JOIN rather than a sub-select? It would be faster to
execute.
> So, a two part query:
> 1st find ALL clones defined by clone.(project plate p_row p_column)
> 2nd find ALL contigs related to each clone.
I think you're doing this the hard way. What's wrong with the
following?
SELECT contig.assembly_date,contig.contig_no,contig.ver
FROM clone JOIN clone_contig USING (clone_id)
WHERE clone.project = '1024' AND
clone.plate = '001' AND
clone.p_row = 'A' AND
clone.p_column = '01';
> WHERE clone.clone_id = (
> SELECT clone.clone_id
> FROM clone
> WHERE clone.project = '1024' AND
> clone.plate = '001' AND
> clone.p_row = 'A' AND
> clone.p_column = '01'
If you want a subselect, then you want:
WHERE clone.clone_id IN (
SELECT clone.clone_id
FROM clone
WHERE clone.project = '1024' AND
clone.plate = '001' AND
clone.p_row = 'A' AND
clone.p_column = '01');
But I do not think that a subselect is actually what you need.
-Josh
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-03-01 17:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-03-31 21:11 Sub select.. Ed <dziablo@lords.com>
2002-02-26 20:42 sub SELECT Charles Hauser <chauser@acpub.duke.edu>
2002-03-01 17:42 ` Re: sub SELECT Josh Berkus <josh@agliodbs.com>
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