Hi Michael! Before the holiday you tried to help me with a problem that I had over-simplified and made more confusing than it was. I'll now try again, and also by giving the columns their right names.
My table consists of parsed outputs from a bioinformatics tool called blast. Every 'sbj_name' (which can be full sentences and probably unsuitable for '=' with other sbj_names) has one 'sbj_count' and at least one 'hsp_count'. The higher the 'score' the lower the 'sbj_count'. My perl program, which uses the table, needs a certain order of the query output to work. It should also be capable of adding new data into the table. After adding new data the query output should have the same structure, thus (only) column sbj_count needs to be updated. For example, this is my old data: sbj_name sbj_count hsp_count 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 this is new: d 1 1 1000 d 1 2 400 e 2 1 900 e 2 2 500 f 3 1 700 g 4 1 600 If I now would ask the same query (SELECT * from table ORDER BY sbj_count,hsp_count) that gave the correct output of the new data I would get: 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 Thus the new sbj_names share sbj_count with the old sbj_names. Instead I want SELECT * from table ORDER BY sbj_count,hsp_count: d 1 1 1000 d 1 2 400 a 2 1 900 a 2 2 500 e 3 1 900 e 3 2 500 b 4 1 800 c 5 1 700 c 5 2 600 c 5 3 500 f 6 1 700 g 7 1 600 As you can see sbj_count has been updated based on the score value, but has also given the same sbj_count value to all the same sbj_name, and thus kept them together in the output. Hope this one is a bit clearer. Many thanks! Marcus On Sat, 2004-12-18 at 21:25, Michael Stassen wrote: > 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...: > > Perhaps you have simplified too much, then, as I simply do not understand > what you want. Comments and questions below. > > > 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', > > How do you achieve this? Is the full really an ID for name, or does it > change according to score? > > > but for my program I have to keep every 'name' > > next to each other, with ascending 'part' number. > > Hence "ORDER BY name, part" or "ORDER BY full, part". OK. > > > I don't want to use > > 'name' as an identifier since they can be long sentences. > > Then name should be in a separate table, linked by its id (full?). > > > 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 > > OK. > > > 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 > > Wait a minute! You said you have a "one-to-one relationship between 'name' > and 'full'", but a full of 1 means name is 'a', not 'd'. > > > And after some manipulation (that I hope someone can help me with) I > > want a query that gives 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 > > You start with 6 rows and add 6 rows. That's 12 rows, but your desired > output has only 10 rows. How do you determine which rows to keep and which > to exclude? > > Looking more closely, I see that this is simply not the data you inserted -- > every row starting with the third has the wrong full number. Surely you are > not saying that you want a query which returns numbers which don't match the > actual values in the column, are you? Are you changing all the full values > after the second insert? > > > 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 > > How did you get this? This output cannot have come from the same query, as > it is not ordered by full, part. Using your query, you should get > > +------+------+------+-------+ > | name | full | part | score | > +------+------+------+-------+ > | a | 1 | 1 | 900 | > | d | 1 | 1 | 1000 | > | d | 1 | 2 | 400 | > | a | 1 | 2 | 500 | > | e | 2 | 1 | 900 | > | b | 2 | 1 | 800 | > | e | 2 | 2 | 500 | > | c | 3 | 1 | 700 | > | f | 3 | 1 | 700 | > | c | 3 | 2 | 600 | > | c | 3 | 3 | 500 | > | g | 4 | 1 | 600 | > +------+------+------+-------+ > 12 rows in set (0.01 sec) > > At this point, it appears to me that you want to change full according to > the new rows, but full,part is apparently your id! That would be a bad > idea, so perhaps I've misunderstood. I'd suggest you back up and tell us > what you really want to accomplish, without the simplification. Then maybe > someone can suggest an effective solution. > > Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
