Thread: Problem with view

Problem with view

From
Wenjin Zheng
Date:
Hi,

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
this:

"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
(707)469-2353
email: wenjin.zheng@lsbc.com 



Re: Problem with view

From
wieck@debis.com (Jan Wieck)
Date:
> 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;"
   Views  are  known to have severe problems with aggregates and   grouping.  And once again  these  are  problems  we
want to   tackle with the parse-/querytree overhaul.
 
   Oh man - this is one of the most important things I see.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #




Re: Problem with view

From
Tom Lane
Date:
wieck@debis.com (Jan Wieck) writes:
>     Views  are  known to have severe problems with aggregates and
>     grouping.  And once again  these  are  problems  we  want  to
>     tackle with the parse-/querytree overhaul.

>     Oh man - this is one of the most important things I see.

Yup.  Outer joins are waiting on that work, too.
        regards, tom lane


Re: Problem with view

From
Don Baccus
Date:
At 05:42 PM 4/3/00 -0400, Tom Lane wrote:
>wieck@debis.com (Jan Wieck) writes:
>>     Views  are  known to have severe problems with aggregates and
>>     grouping.  And once again  these  are  problems  we  want  to
>>     tackle with the parse-/querytree overhaul.
>
>>     Oh man - this is one of the most important things I see.
>
>Yup.  Outer joins are waiting on that work, too.

Are you saying outer joins won't happen until the overhaul?  Does
this mean they're not happening in 7.1 or that 7.1 will just be
in the far distance?



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 


Re: Problem with view

From
Tom Lane
Date:
Don Baccus <dhogaza@pacifier.com> writes:
>> Yup.  Outer joins are waiting on that work, too.

> Are you saying outer joins won't happen until the overhaul?

Right.

> Does
> this mean they're not happening in 7.1 or that 7.1 will just be
> in the far distance?

7.1 isn't happening next week, no.  I'm guessing maybe late summer.

I don't think we'll release 7.1 until the overhaul is done.
        regards, tom lane