Skip site navigation (1) Skip section navigation (2)

Problem with view

From: Wenjin Zheng <wenjin(dot)zheng(at)lsbc(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Problem with view
Date: 2000-04-03 17:50:29
Message-ID: E3E65B3C4E12D311BE6900A0C9EC2FA11554C9@GENOMICS1 (view raw or whole thread)
Lists: pgsql-hackers

I have got another problem.  I have a table(table tempOne) that has two
fields, seqid(int) and phredscore(int).  They are one to many relationship,
e.g. one seqid has many phredscore.  I would like to get the row that has
the max(phredscore).  So I first created a view as follow:
"create view maxphred as select seqid, max(phredscore) as phredscore from
tempOne group by seqid;"

Then I try to get the rows that corresponding to the top phredscore.  I did

"select tempOne.* from tempOne, maxphred where tempOne.seqid=maxphred.seqid
and tempOne.phredscore=maxphred.phredscore;"

I got some weird stuff back which obviously is wrong.  However if I create a
table maxphred rather than a view, I get the correct result.  There might be
something missing for the view that I did not know of.  Does anyone know
that my query with view did not work.

Your help will be greatly appreciated.

Wenjin Zheng
Bioinformatic Analyst
Biosource Technologies, Inc.
3333 Vaca Valley Parkway
Vacaville, CA 95688
email: wenjin(dot)zheng(at)lsbc(dot)com 


pgsql-hackers by date

Next:From: Tom LaneDate: 2000-04-03 18:48:23
Subject: Should pg_dump refuse to run if DB has different version?
Previous:From: Adriaan JoubertDate: 2000-04-03 17:36:48
Subject: Re: BIT datatype (Fixed)

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group