[SQL] can't get the order I want after inserting new rows
Hi! I have a problem with getting the order I want on a table after new rows have been inserted. I try to simplify it...: I want to have a one-to-one relationship between 'name' and 'full'. Every 'name' (or'full') have one or more 'parts'. The higher the 'score' the lower the 'full', but for my program I have to keep every row with the same 'name' next to each other, with ascending 'part' number. I don't want to use 'name' as an identifier since they can be long sentences. In other words, I have this old table: SELECT * from table ORDER BY full,part; namefullpartscore --- --- - a 1 1 900 a 1 2 500 b 2 1 800 c 3 1 700 c 3 2 600 c 3 3 500 and I insert these rows into the same table: (there's nothing I can do about these 'full' values) d 1 1 1000 d 1 2 400 e 2 1 900 e 2 2 500 f 3 1 700 g 4 1 600 And after some manipulation (that I hope someone can help me with) I want the query above (SELECT * from table ORDER BY full,part) to give this: d 1 1 1000 d 1 2 400 a 2 1 900 e 3 1 900 b 4 1 800 c 5 1 700 c 5 2 600 c 5 3 500 f 6 1 700 g 7 1 600 rather than a 1 1 900 a 1 2 500 d 1 1 1000 d 1 2 400 b 2 1 800 e 2 1 900 e 2 2 500 c 3 1 700 c 3 2 600 c 3 3 500 f 3 1 700 g 4 1 600 Very grateful for any feedback! Marcus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] sql can i substitute
hi table: name varchar(10) fruit integer i want to write an sql statement like this: select fruit from table which should return 'good' if fruit = 1 and 'bad' if fruit =2 and 'rotten' if fruit =3 can it be done? kg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [despammed] [SQL] sql can i substitute
am 17.12.2004, um 16:55:45 +0530 mailte Kenneth Gonsalves folgendes: > hi > > table: > > name varchar(10) > fruit integer > > i want to write an sql statement like this: > > select fruit from table > > which should return 'good' if fruit = 1 and 'bad' if fruit =2 and 'rotten' if > fruit =3 > > can it be done? Okay: test=# select * from fruit; name | fruit +--- Apple | 1 Banana | 2 Cherry | 3 (3 Zeilen) test=# select name, fruit, case test-# when fruit = 1 then 'good' test-# when fruit = 2 then 'bad' test-# when fruit = 3 then 'rotten' end from fruit; name | fruit | case +---+ Apple | 1 | good Banana | 2 | bad Cherry | 3 | rotten (3 Zeilen) Please read http://www.postgresql.org/docs/7.4/interactive/functions-conditional.html#AEN11381 Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Tel. NL Heynitz: 035242/47212 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [despammed] [SQL] sql can i substitute
On Friday 17 December 2004 05:15 pm, Andreas Kretschmer wrote: > test=# select * from fruit; > name | fruit > +--- > Apple | 1 > Banana | 2 > Cherry | 3 > (3 Zeilen) > > > test=# select name, fruit, case > test-# when fruit = 1 then 'good' > test-# when fruit = 2 then 'bad' > test-# when fruit = 3 then 'rotten' end from fruit; > name | fruit | case > +---+ > Apple | 1 | good > Banana | 2 | bad > Cherry | 3 | rotten > (3 Zeilen) thanks - opened up a whole new world for me. One more thing, the values come under the column 'case', can i avoid having the column 'case' and get the 'good', 'bad' and 'rotten' under the column 'fruit'? kg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [despammed] [SQL] sql can i substitute
am 17.12.2004, um 17:48:15 +0530 mailte Kenneth Gonsalves folgendes: > > thanks - opened up a whole new world for me. One more thing, the values come > under the column 'case', can i avoid having the column 'case' and get the > 'good', 'bad' and 'rotten' under the column 'fruit'? Yes, simple: test=# select name, case test-# when fruit = 1 then 'good' test-# when fruit = 2 then 'bad' test-# when fruit = 3 then 'rotten' end as fruit from fruit; name | fruit + Apple | good Banana | bad Cherry | rotten (3 Zeilen) Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Tel. NL Heynitz: 035242/47212 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] can't get the order I want after inserting new rows
Marcus Claesson wrote: Hi! I have a problem with getting the order I want on a table after new rows have been inserted. I try to simplify it...: In other words, I have this old table: SELECT * from table ORDER BY full,part; namefullpartscore --- --- - a 1 1 900 a 1 2 500 and I insert these rows into the same table: (there's nothing I can do about these 'full' values) d 1 1 1000 d 1 2 400 And after some manipulation (that I hope someone can help me with) I want the query above (SELECT * from table ORDER BY full,part) to give this: d 1 1 1000 d 1 2 400 a 2 1 900 Just looking at the start of your output, you are missing some rows (a/1/1) and have replaced others (a/2/1 isn't in your data). Are you trying to summarise, and if so by what? Can you explain how you would do this by hand. Could you provide the actual table definition? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sql can i substitute
On Fri, 17 Dec 2004 16:55:45 +0530 Kenneth Gonsalves <[EMAIL PROTECTED]> wrote: > i want to write an sql statement like this: > > select fruit from table > > which should return 'good' if fruit = 1 and 'bad' if fruit =2 and > 'rotten' if fruit =3 An alternative to Andreas' suggestion would be to create a simple lookup table and join them. This is good if the real life example can get larger and/or the list can change and you don't want to modify code every time it does. [totally made up output] fstate_id | fstate_name --+- 1 | good 2 | bad 3 | rotten SELECT fstate.fstate_name AS "Fruit state" FROM table, fstate WHERE table.fstate_id = fstate.fstate_id; Now you can easily add another state: INSERT INTO fstate VALUES (4, 'smelly'); -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] sql can i substitute
On Friday 17 December 2004 06:12 pm, D'Arcy J.M. Cain wrote: > An alternative to Andreas' suggestion would be to create a simple lookup > table and join them. This is good if the real life example can get > larger and/or the list can change and you don't want to modify code > every time it does. yes, but in this case the list wont change, and i'm trying to port mysql to pgsql without disturbing as far as possible the mysql queries. basically replacing some 'set' and 'enum' datatypes - one table has six of thes, and adding six tables is not on kg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [despammed] [SQL] sql can i substitute
On Friday 17 December 2004 05:54 pm, Andreas Kretschmer wrote: > Yes, simple: > > test=# select name, case > test-# when fruit = 1 then 'good' > test-# when fruit = 2 then 'bad' > test-# when fruit = 3 then 'rotten' end as fruit from fruit; can one do the same thing for an 'insert' statement? kg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [despammed] [SQL] sql can i substitute
O Kenneth Gonsalves έγραψε στις Dec 17, 2004 : > On Friday 17 December 2004 05:54 pm, Andreas Kretschmer wrote: > > > Yes, simple: > > > > test=# select name, case > > test-# when fruit = 1 then 'good' > > test-# when fruit = 2 then 'bad' > > test-# when fruit = 3 then 'rotten' end as fruit from fruit; > > can one do the same thing for an 'insert' statement? its an expression, so yes, INSERT INTO foo3 VALUES (2,case when 't' then 'bar' else 'foo' end); but whats the point? > > kg > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] can't get the order I want after inserting new rows
Hi Richard, > Just looking at the start of your output, you are missing some rows > (a/1/1) and have replaced others (a/2/1 isn't in your data). Yes, that's exactly it. There's no mistake. The only column I need to update is 'full', by giving every row a new 'full', based on its 'score'. The tricky thing is that if a 'name' has several 'parts', then all those 'parts' should have the same new 'full' value. Not sure if I'm making much sense here...Just ask away if it's still unclear. > Are you trying to summarise, and if so by what? > Can you explain how you would do this by hand. > Could you provide the actual table definition? I'm trying to summarize parsed outputs from the bioinformatics application 'blast', where 'name' is hit-name, 'full' is hit, and 'part' is sub-hits called HSPs (within the same hit). Score is score. If I was doing it by hand I would sort the rows descending according to score, and give them new 'full' values, starting from 1. But if a 'name' has more than one 'part' I would give them the same new 'full' value as its first 'part', even if they would have lower scores. Actually, I could add the new data in two different ways. Either as before: d 1 1 1000 d 1 2 400 e 2 1 900 e 2 2 500 f 3 1 700 g 4 1 600 or (by using the old data's highest 'full' as offset) d 3 1 1000 d 3 2 400 e 5 1 900 e 5 2 500 f 6 1 700 g 7 1 600 The table defintion would be CREATE TABLE table(name TEXT NOT NULL,full INTEGER,part INTEGER) Hope I haven't confused you more...;) Marcus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] sql can i substitute
On Fri, Dec 17, 2004 at 16:55:45 +0530, Kenneth Gonsalves <[EMAIL PROTECTED]> wrote: > hi > > table: > > name varchar(10) > fruit integer > > i want to write an sql statement like this: > > select fruit from table > > which should return 'good' if fruit = 1 and 'bad' if fruit =2 and 'rotten' if > fruit =3 > > can it be done? Yes; use a CASE statement. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] sql can i substitute
On Fri, Dec 17, 2004 at 18:22:48 +0530, Kenneth Gonsalves <[EMAIL PROTECTED]> wrote: > On Friday 17 December 2004 06:12 pm, D'Arcy J.M. Cain wrote: > > > An alternative to Andreas' suggestion would be to create a simple lookup > > table and join them. This is good if the real life example can get > > larger and/or the list can change and you don't want to modify code > > every time it does. > > yes, but in this case the list wont change, and i'm trying to port mysql to > pgsql without disturbing as far as possible the mysql queries. basically > replacing some 'set' and 'enum' datatypes - one table has six of thes, and > adding six tables is not on You might be better off using domains and a constraint to implement set types. Storing numbers instead of names will save a little space, but unless you are having some problems with resources, using the strings directly will be simpler. ---(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] Table History
On Dec 17, 2004, at 1:23 AM, Richard Sydney-Smith wrote: I expect this has been done MANY times and I wonder if a general purpose trigger exists or if not then can someone point me to an example set of triggers? I'm not aware of a "general purpose" trigger for this. If you just want some extra trigger examples other than what is in the documentation, there is a test file in the distribution with quite a few: src/test/regress/sql/plpgsql.sql Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Table History
Here is a example of a trigger function CREATE OR REPLACE FUNCTION public.ipinfo_trg() RETURNS trigger AS 'DECLARE dhcpvarchar:=\'DHCP\'; rtype varchar:=\'RAS\'; BEGIN if NEW.ipaddress != dhcp then if OLD.ipaddress != dhcp then if OLD.atype != rtype then insert into vpnip(ipaddress) values(inet(OLD.ipaddress)); else insert into rasip(ipaddress) values(inet(OLD.ipaddress)); end if; else end if; else if OLD.ipaddress != dhcp then if OLD.atype != rtype then insert into vpnip(ipaddress) values(inet(OLD.ipaddress)); else insert into rasip(ipaddress) values(inet(OLD.ipaddress)); end if; else end if; END IF; Return NEW; END; ' LANGUAGE 'plpgsql' VOLATILE; Here is a example of how to call the trigger function from your table CREATE TRIGGER update_ipinfo_trg AFTER UPDATE ON public.ipinfo FOR EACH ROW EXECUTE PROCEDURE public.ipinfo_trg(); -Original Message- From: John DeSoi [mailto:[EMAIL PROTECTED] Sent: Friday, December 17, 2004 10:38 AM To: Richard Sydney-Smith Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Table History On Dec 17, 2004, at 1:23 AM, Richard Sydney-Smith wrote: > I expect this has been done MANY times and I wonder if a general > purpose trigger exists or if not then can someone point me to an > example set of triggers? I'm not aware of a "general purpose" trigger for this. If you just want some extra trigger examples other than what is in the documentation, there is a test file in the distribution with quite a few: src/test/regress/sql/plpgsql.sql Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] can't get the order I want after inserting new rows
Marcus Claesson wrote: Hi Richard, Just looking at the start of your output, you are missing some rows (a/1/1) and have replaced others (a/2/1 isn't in your data). Yes, that's exactly it. There's no mistake. The only column I need to update is 'full', by giving every row a new 'full', based on its 'score'. The tricky thing is that if a 'name' has several 'parts', then all those 'parts' should have the same new 'full' value. Not sure if I'm making much sense here...Just ask away if it's still unclear. OK - so if I re-arrange the columns: (name*, part*, score, full) The primary key is (name,part), (score) is what you measure and (full) is just for ordering. So - you want to update "full" so it reflects the sortorder something like: SELECT name, max(score) FROM table GROUP BY name ORDER BY max(score) DESC; You can then get the order you want with something like: SELECT t1.name, t1.part, t1.score, t2.order_score FROM table t1, ( SELECT name, max(score) AS order_score FROM table GROUP BY name ) AS t2 WHERE t1.name = t2.name ORDER BY t2.order_score DESC, t1.part ASC; Write a small plpgsql function to process the table in that order and update "full" accordingly. Actually, I'd add a SERIAL primary key and have a separate table for "full" - that way you can just delete the sorting values and replace them in one go. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] get branches & childs from database
Hello newsgroup! I'm trying to build up a menu by sending ONE query to database. Afterwards the result is used by PEAR::HTML_Menu get a html-structered menu. db-structure of gallery: ++---+--+--++---+ | id | title | date | root | parent | level | ++---+--+--++---+ | 5 | A | XX | 5 | 0| 1 | | 1 | A2| XX | 5 | 5| 2 | | 2 | A1| XX | 5 | 5| 2 | | 3 | A11 | XX | 5 | 2| 3 | | 4 | A12 | XX | 5 | 2| 3 | | 6 | A21 | XX | 5 | 1| 3 | | 7 | A211 | XX | 5 | 6| 4 | | 8 | B | XX | 8 | 0| 1 | | 9 | B1| XX | 8 | 8| 2 | ++---+--+--++---+ following limitations are set: - for root-nodes parent=0 have to be set - result should by available after sending ONE query - child of the specified gallery should be delivered - branch of the specified gallery should be delivered menu-structure after the query for id=6: + A |-> A1(optional, no need to be but nice to have) |-> A2 |-> A21(specified id) |-> A211 + B Therefore I need the following result after sending the query: ++---++ | id | title | parent | ++---++ | 8 | B | 0| | 6 | A21 | 1| | 7 | A211 | 6| | 1 | A2| 5| | 5 | A | 0| | 2 | A1| 5| (optional) ++---++ I currently use following query: SELECT id,title,parent,FROM gallery WHERE (id=root OR root IN (SELECT root FROM gallery WHERE id=6)) AND gall_level <= (SELECT level FROM gallery WHERE id=6)+1 ORDER BY level ASC,date DESC With this query I get the additional branch under A1, but that's not what I want. I don't know what to do. I'm able to use views, subselects, rules (of course) and furthermore functions (plpgsql). I'd be pleased, if there are any ideas Thanks, Marcus. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Updating column to link one table to another
I have two tables, with a many to one relationship. Table InfrequentTable - timestamp ts - int infrequentId (newly added key) Table FrequentTable(Many FrequentTable entries per one InfrequentTable entry) - timestamp ts - int infrequentId (newly added column) The link is chronological, in that when an InfrequentTable entry happens, then subsequent FrequentTable entries should be linked to it, until the next InfrequentTable event happens, in which case old FrequentTable entries are left alone, but new ones are linked to the newest InfrequentTable entry. Now, I've added the infrequentId columns to both, as an optimization, so that I can quickly find the InfrequentTable entry for a given FrequentTable entry. I've done this because I want to speed up SELECTs. Any new INSERTs are working just fine. But, all my legacy entries, are not linked up yet. I need to know how I can UPDATE the FrequentTable rows, where their infrequentId is zero, to point at the last InfrequentTable entry whose timestamp ts is before its own timestamp ts. Can anyone help me with this? Thank you. - Mark Collette ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
