MySQL Stored Procedure Getting distinct count, displaying latest records only -


i have following inside stored procedure retrieves unique records player names have faction of neutral:

select     count(distinct name) @neutcount      dim5players      faction ='neutral';  update dim5stats     set value = @neutcount      function = 'neutral'; 

this works find , dandy. problem have field called date well.
want select lastest date of records listed in count instead of random record unique "name" record.

this history table, , records daily changes of records, name can appear several times. need count latest records have faction of neutral latest records only. people change factions time time. care latest faction.

this structure:

create table `dim5players` ( `id` char(64) not null, `name` varchar(45) not null, `rank_name` varchar(20) null default null, `level` int(11) not null, `defender_rank_id` int(11) null default null, `faction` varchar(15) not null, `organization` varchar(100) null default null, `date` date not null, `updated` bit(1) not null, unique index `id_unique` (`id`) using hash, index `name_index` (`name`) using hash, index `date_index` (`date`) using hash, index `updated_index` (`updated`) using hash, index `faction_index` (`faction`) using hash        )       collate='utf8_general_ci'       engine=innodb; 

after discussion michael think figured out needs:

  1. "i want last updated record of each name"

    select       name ,     max(date) last_date      dim5players      faction ='neutral' group     name 
  2. "i want count latest date on each name still holds faction of neutral"

    select     count(last_date) (     select           name ,         max(date) last_date              dim5players              faction ='neutral'     group         name ) tmp 

@michael : let me know if understood requirements correctly


Comments