Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: INSERT counter field to a table

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How do you define a records priority?
    Date for the record to be actioned by?

    A little more explanation needed please
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2004
    Posts
    144
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT people.* 
         , ( select count(*) + 1
               from people
              where age < t.age )
      FROM people AS t
    ORDER BY age
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    George
    Home | Blog

  7. #7
    Join Date
    May 2004
    Posts
    144
    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` );
    Last edited by bono56; 09-26-07 at 12:44.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ur? u?

    eye think ewe will have a problem as soon as ewe add another person to that table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2004
    Posts
    144
    why? beacause of change in primary key?

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I am wondering why ewe would not utilise the statement that has already been granted to yourself.
    Last edited by aschk; 09-27-07 at 10:49.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •