Marcus Claesson <[EMAIL PROTECTED]> wrote on 01/10/2005 02:00:37 PM:
> 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 > So you ARE having problems trying to change your sbj_count and hsp_count columns after adding information to your table your so that data is re-ranked according to some rule(s) you have. Exactly, how do you determine the new ranking? I could make a guess based on your data but I would probably be wrong. Walk me through this process, please. I give you these data points: (sbjname, score): (a,500), (a,900), (b,800), (c,500), (c,600), (c,700), (d,400), (d,1000), (e,500), (e,900), (f,700), (g,600) Could you please explain to me how to generate(create) the sbj_count and hsp_count values for each row? As I said, I can probably guess but I want the "official" method from you. I can help you write the SQL to do it but I need to know what you need to do. Shawn Green Database Administrator Unimin Corporation - Spruce Pine <big snip>
