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
