INSERT counter field to a table

bono56 · #1 (**permalink**) 09-24-07, 11:38

hi
i add a field name `priority` to myTable.
i want to save priority of records in this field when select them by sepecfic criteria & order by. like a counter 1...n.
this means priority field for the first selected record is 1 & for n`th selected record is n.
is it possible to make it by mySQL? doing this task by php becase of number of records is so slow.

gvee · #2 (**permalink**) 09-25-07, 04:25

How do you define a records priority?
Date for the record to be actioned by?

A little more explanation needed please

healdem · #3 (**permalink**) 09-25-07, 05:44

if the priority is going to be predetermined then you can use a column, called say SortSeq, and use that as an indexed column to display rows in a predetermined order. You cna set the SortSeq values and then using that as an indexed column (perhaps in conjunction with an Alphabetic column you can vary the display sequence as required

if however you need to create this sort sequence on the fly then I'm not sure how you could do that. how can you create an index based on a sort sequence the user wants to pick seemingly at random. unless you have some whizzy algolrhytm which could create a sort value on the fly.. even if you do that it will be horrendously slow as the result would be unindexed, unless you put the values into say a temporary table

before you start delving into this are you certain you are using indexes appropriately, are you certain your joins are formed correctly or most efficiently, are you applying the where criteria in the right manner. The slowness of the app may be down to other factors, such as server load (web or data), poor / non optimised design.

HTH

bono56 · #4 (**permalink**) 09-26-07, 07:45

hi
sorry if i couldnt explain well, maybe a sample can make it more clear:

suppose table with this data:

id fname age
-- ----- ---
1 fred 26
2 chris 19
3 sara 36
4 john 23
5 billy 31

now i select them like this:

SELECT * FROM people ORDER BY age
id fname age
-- ----- ---
2 chris 19
4 john 23
1 fred 26
5 billy 31
3 sara 36

& i want to add a field (priority) & set it like this:

id fname age priority
-- ----- --- --------
2 chris 19 1
4 john 23 2
1 fred 26 3
5 billy 31 4
3 sara 36 5

r937 · #5 (**permalink**) 09-26-07, 07:57

Code:

SELECT people.* 
     , ( select count(*) + 1
           from people
          where age < t.age )
  FROM people AS t
ORDER BY age

gvee · #6 (**permalink**) 09-26-07, 08:07

Sounds like you want what's called a "running total"...

Why does age denote priority? I know it's probably just an example but it's very misleading. Real sample data will more than likely help!

bono56 · #7 (**permalink**) 09-26-07, 09:38

tanX r937, ur statement gave me a soultion, what do u think about it?

Code:

#create a mirror table with priority field:
CREATE TABLE `peopleWithPriority` (
  `id` int(10) unsigned NOT NULL,
  `fname` varchar(100) NOT NULL default '',
  `age` varchar(20) NOT NULL default '0',
  `priority` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`priority`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

#insert to new table(with priority):
INSERT INTO `peopleWithPriority` (`id`, `fname`, `age`) 
SELECT `id`, `fname`, `age` from people ORDER BY age;

#change primary
#remove autoincreament
ALTER TABLE `peoplewithpriority` CHANGE `priority` `priority` INT( 10 ) UNSIGNED NOT NULL;
#change primary
ALTER TABLE `peoplewithpriority` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `id` ) ;
ALTER TABLE `peoplewithpriority` CHANGE `id` `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `peoplewithpriority` ADD INDEX ( `priority` );