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:
"i want last updated record of each name"
select name , max(date) last_date dim5players faction ='neutral' group name"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
Post a Comment