Thread: 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; name full part score ---- --- --- ----- 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
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; > > name full part score > ---- --- --- ----- > 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
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
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 BYname 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