pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Sub 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