[SQL] How do I concatenate row-wise instead of column-wise?
I have a table like this: SELECT * FROM old_tab; id|descr --- 1|aaa 1|aaa 1|bbb 2|ccc 2|bbb 3|ddd 3|ddd 3|eee 3|fff 4|bbb etc... And I want a new table where the descr is concatenated row-wise like this: SELECT * FROM new_tab; id|descr -- 1|aaa;bbb 2|ccc;bbb 3|ddd;eee;fff 4|bbb etc... This is the closest I get: UPDATE new_tab SET descr = old_tab.descr||' ; '||old_tab.descr from old_tab where old_tab.id=new_tab.id; UPDATE 4 SELECT * FROM new_tab ; id | descr +--- 1 | aaa ; aaa 2 | ccc ; ccc 3 | ddd ; ddd 4 | bbb ; bbb etc... Thus, the concatenating operator never works on other rows than the present. How can I get around that and still stick to the postgresql syntax? Regards Marcus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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])
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
