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 


Mon, 3 Apr 2000 10:50:29 -0700 
Wenjin Zheng <wenjin.zheng@lsbc.com>
Problem with view
> 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) #



Mon, 3 Apr 2000 23:10:36 +0200 (CEST)
wieck@debis.com (Jan Wieck)
Re: Problem with view
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

Mon, 03 Apr 2000 17:42:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problem with view 
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 Service and other goodies at
  http://donb.photo.net.

Mon, 03 Apr 2000 17:43:51 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Problem with view 
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

Mon, 03 Apr 2000 21:27:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problem with view 