Thread: Problem with view
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
> 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) #
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
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.
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