[SQL] Help with a "recursive" query
Hi, I've got a table like this : idparent description 0100 xx 0200 xx 0301 xx 0402 xx 0501 xx 0605 xx 0703 xx 0807 xx 0900 xx 1002 xx I want to get all tuples which have id=01 as their parent or ancestor, i.e. : 03, 05, 06, 07, 08 this to represent a sort of n-tree : 01 / \ /\ / \ 03 05 / \ /\ / \ 07 06 | | | 08 this example is simple because each node has at most one child, but in my real data there's no such limit. any idea ? thanks in advance Jerome Alet ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] text vs varchar
On Tue, 18 Jun 2002, Josh Berkus wrote: > Wei, > > > Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR > > datatype with a maximum length, especially when I do searches on them? > > Yes. You can't index TEXT because it's of potentially unlimited length. Well indexing text works fine for me. Table "repdat" Column |Type | Modifiers -+-+--- vslid | integer | vslname | character varying(15) | orderno | integer | not null date_in | timestamp without time zone | port_landed | character varying(15) | subject | text| catid | integer | dynacom=# EXPLAIN ANALYZE SELECT subject from repdat where subject='UTM FOR SPECIAL SURVEY JUNE2000'; NOTICE: QUERY PLAN: Index Scan using repdat_subject_idx on repdat (cost=0.00..7.40 rows=1 width=28) (actual time=0.05..0.06 rows=1 loops=1) Total runtime: 0.10 msec EXPLAIN dynacom=# dynacom=# SET enable_indexscan = off; SET VARIABLE dynacom=# EXPLAIN ANALYZE SELECT subject from repdat where subject='UTM FOR SPECIAL SURVEY JUNE2000'; NOTICE: QUERY PLAN: Seq Scan on repdat (cost=0.00..388.59 rows=1 width=28) (actual time=0.03..8.14 rows=1 loops=1) Total runtime: 8.19 msec EXPLAIN dynacom=# > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Aggregates not allowed in WHERE clause?
Hi all, I have a table (lv) with a field "semester" and I'm trying to fish out all rows which have a value corresponding to a max() value of another table's (lf_sem) "semester" field. The intention is to keep a value with the current term in lf_sem so I can get all rows which concern this term. Example tables: lv == semester | kvvnr -+-- 2001ss | 4185 2001ss | 4203 2002ws | 4163 2002ws | 4190 lf_sem == semester 2001ws 2002ss 2002ws At first I tried this query: SELECT kvvnr FROM lv, lf_sem WHERE lv.semester = max(lf_sem.semester); This yields the message: 'Aggregates not allowed in WHERE clause'. Next I tried this one: SELECT kvvnr, max(lf_sem.semester) AS akt_semester FROM lv, lf_sem WHERE lv.semester = akt_semester; Now I got: 'Attribute 'akt_semester' not found' Is there another way to get what I want? Clueless, joachim ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Aggregates not allowed in WHERE clause?
> Next I tried this one: > > SELECT kvvnr, max(lf_sem.semester) AS akt_semester > FROM lv, lf_sem > WHERE lv.semester = akt_semester; > > Now I got: 'Attribute 'akt_semester' not found' > > Is there another way to get what I want? What about: SELECT kvvnr FROM lv WHERE semester = (SELECT MAX(semester) FROM lf_sem); Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Aggregates not allowed in WHERE clause?
On 19 Jun 2002, Joachim Trinkwitz wrote: > Hi all, > > I have a table (lv) with a field "semester" and I'm trying to fish out all > rows which have a value corresponding to a max() value of another > table's (lf_sem) "semester" field. The intention is to keep a value > with the current term in lf_sem so I can get all rows which concern > this term. > > Example tables: > > lv > == > semester | kvvnr > -+-- > 2001ss | 4185 > 2001ss | 4203 > 2002ws | 4163 > 2002ws | 4190 > > lf_sem > == > semester > > 2001ws > 2002ss > 2002ws > > At first I tried this query: > > SELECT kvvnr > FROM lv, lf_sem > WHERE lv.semester = max(lf_sem.semester); > > This yields the message: 'Aggregates not allowed in WHERE clause'. > > Next I tried this one: > > SELECT kvvnr, max(lf_sem.semester) AS akt_semester > FROM lv, lf_sem > WHERE lv.semester = akt_semester; > > Now I got: 'Attribute 'akt_semester' not found' > > Is there another way to get what I want? SELECT lv.kvvnr,lv.semester from lv where lv.semester = (select max(semester) from lf_sem); > > Clueless, > joachim > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
