Thanks for your help guys!
By looking at your solutions I understand how it's meant to work. The
problem is that I'm using the older MySQL 3.23.58. And I'm bound to
stick with that version since it's standard in most Redhat
distributions, and the program I'm writing will be used on several of
those platforms. Thus I get syntax error messages when I run some of
your suggested queries, for example
mysql> UPDATE blast, ranks SET blast.sbj_count = ranks.rank WHERE
blast.sbj_name=ranks.sbj_name;
ERROR 1064: You have an error in your SQL syntax near ' ranks SET
blast.sbj_count = ranks.rank WHERE blast.sbj_name=ranks.sbj_name' at
line 1
and
mysql> CREATE TEMPORARY TABLE tmpGenSbjCount (sbj_count int
auto_increment, sbj_name varchar(255) primary_key, score int not null);
ERROR 1064: You have an error in your SQL syntax near 'primary_key,
score int not null)' at line 1
They way I eventually made it work was
CREATE TABLE temp_table
SELECT sbj_name,max(score)
AS order_score FROM table
GROUP BY sbj_name
(indexing both sbj_name columns to speed up the query below)
CREATE INDEX sbj_name_index
ON temp_table(sbj_name(30))
CREATE INDEX sbj_name_index
ON $table(sbj_name(30))
SELECT table.sbj_name, table.sbj_count, table.hsp_count,
table.score,temp_table.order_score
FROM table,temp_table
WHERE table.sbj_name = temp_table.sbj_name
ORDER BY temp_table.order_score DESC, table.sbj_count ASC
I then looped (perl DBI) over the result of the latter query updating
sbj_count values:
my $new_sbj_count = 0;
my $old_sbj_name = "";
foreach my $row (@{$update_set}) {
$new_sbj_count++ if ($row->{sbj_name} ne $old_sbj_name);
$dbh->do("UPDATE $table SET sbj_count=$new_sbj_count WHERE
sbj_name='$row->{sbj_name}' AND hsp_count='$row->{hsp_count}'");
$old_sbj_name = $row->{sbj_name};
}
I know I'm not using especially the sbj_count column in an optimal way,
but since the rest of my program is doing other stuff with this table, I
try to stick to only one table and keep the database structure (if you
can call it that ;-)) as simple as possible.
Many thanks for all your help and effort!!
Marcus
On Tue, 2005-01-11 at 17:08, [EMAIL PROTECTED] wrote:
> Marcus Claesson <[EMAIL PROTECTED]> wrote on 01/11/2005 11:05:49
> AM:
>
> > Thanks for helping!
> >
> > > 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.
> >
> > Both the old and new ranking are based on score values. The highest
> > score get the lowest sbj_count, but sbj_names must be ordered next to
> > each other (a a b b c c etc) with ascending hsp_count values. Thus,
> > (sbj_count,sbjname, score,hsp_count)
> > (1,d,1000,1)
> > (1,d,400,2)
> > (2,a,900,1)
> > (2,a,500,2)
> > (3,e,900,1)
> > (3,e,500,2)
> > (4,b,800,1)
> > (5,c,700,1)
> > (5,c,600,2)
> > (5,c,500,3)
> > (6,f,700,1)
> > (7,g,600,1)
> >
> >
> > Hope that helps...
> >
> > Regards,
> > Marcus
> >
> Perfect! just what I needed. First step. Re-rank all sbjnames according to
> their scores (substitute the actual column size for sbj_name here and in
> following steps).
>
> CREATE TEMPORARY TABLE tmpGenSbjCount (
> sbj_count int auto_increment
> , sbj_name varchar(xxx) primary_key
> , score int not null
> );
>
> INSERT tmpGenSbjCount (sbj_name, score)
> SELECT sbj_name, max(score) as topscore
> FROM datatable
> GROUP BY sbj_name
> ORDER BY topscore desc;
>
> Now, tmpGenSbjCount has ranked all of your sbj_names according to their
> score. The next step is to rank all scores by name to generate their
> hsp_count.
>
> CREATE tmpGenHspCount (
> sbj_count int not null
> , sbj_name varchar(xxx)
> , score int not null
> , hsp_count int auto_increment
> , old_sbj_count int not null
> , old_hsp_count int not null
> , PRIMARY KEY (sbj_count, sbj_name, hsp_count)
> );
>
> INSERT tmpGenHspCount(sbj_count, sbj_name, score, old_sbj_count,
> old_hsp_count)
> SELECT tgsc.sbj_count, tgsc.sbj_name, dt.score, dt.sbj_count, dt.hsp_count
> FROM tmpGenSbjCount tgsc
> INNER JOIN datatable dt
> on tgsc.sbj_name = dt.sbj_name
> ORDER by dt.score DESC;
>
> tmpGenHspCount should now contain the correct (new) sbj_count and
> hsp_counts for each (sbj_name, score) pair.(please check!!) We can use the
> data in that table to update your original data (datatable). I added the
> columns for the "old" values to deal with the situation of duplicate
> scores in your original data. If I didn't, then every duplicate score
> would have been updated to have the SAME hsp_count (not what you wanted).
>
> UPDATE datatable dt
> INNER JOIN tmpGenHspCount tghc
> ON tghc.sbj_name = dt.sbj_name
> AND tghc.score = dt.score
> AND tghc.old_sbj_count = dt.sbj_count
> AND tghc.old_hsp_count = dt.hsp_count
> SET dt.sbj_count = tghc.sbj_count
> , dt.hsp_count = tghc.hsp_count;
>
> Your original data should now be re-ranked and we can drop our temporary
> tables.
>
> DROP TEMPORARY TABLE tmpGenSbjCount, tmpGenHspCount;
>
> I can't say how fast this will run but this _is_ a SQL-only method of
> performing the re-rank you want to achieve. Each tmp table calculation is
> an opportunity for optimization so do an EXPLAIN on each SELECT to check
> that an index is being used as appropriate. This process will not work if
> your version of MySQL does not permit multi-table UPDATEs.
>
> HTH!
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
