mysql - Selecting Max TimeStamp values for each ID within a Parameterized View -

• mysql version 5.5.49, on linux mint (ubuntu 14.04.1 based)

the people table contains of family: jed, david, sarah, , luke. cars table contains 2 cars own. carpeople table contains entry each person rides in car, , contains keys associate personid carid. last cardata table, contains color of car, personid of last painted car, image of paint job, date of paint job, , url see image.

this strange example, idea people associated specific car id's can paint or ride in car. example data inserted has jed (1) , david (2) riding in car 1, while david (2), luke (3), , sarah (4) ride in car 2.

the following query result must contained in view, parameter passed in references personid:

show carid, imageurl, , people ride personid, latest dataentrytime each carid.

my people_concat column isn't outputting correct results, can fix later.

my main issue can't figure out how output data row max dataentrytime each carid within view. changes have tried result in 1 row being outputted, can't figure out how this.

the crucial test query this:

select c.* (select @p1:=2 p) parm , carriderlist c; 

this select view respect user 2, david. instead of 5 rows, 2 should outputted, first of carid 2, , first of carid 1.

running these queries should result in 1 row, 1 containg highest dataentrytime associated personid's:

select c.* (select @p1:=1 p) parm , carriderlist c; select c.* (select @p1:=3 p) parm , carriderlist c; select c.* (select @p1:=4 p) parm , carriderlist c; 

thank you!(sql code below)

/* people in database */  create table people ( personid int(11) not null unique auto_increment, phonenumber varchar(30) not null unique, name varchar(40) not null unique, primary key (personid) ) engine=innodb default charset=utf8;  insert people (phonenumber, name) values ("123-456-7891", "jed"), ("223-456-7891", "david"), ("323-456-7891", "luke"), ("423-456-7891", "sarah"); select * people;  /* cars */  drop table if exists cars; create table cars ( carid int(11) not null unique auto_increment, purchasetime date, primary key (carid) ) engine=innodb default charset=utf8;  insert cars (purchasetime) values ("2016-1-1"),("2016-1-2"); select * cars;  /* carperson: contain entry each person rides in car, , can paint */  drop table if exists carperson; create table carperson ( carpersonid int(11) not null unique auto_increment, carid int(11) not null, carriderid int(11) not null, primary key (carpersonid), foreign key (carid) references cars(carid), foreign key (carriderid) references people(personid) ) engine=innodb default charset=utf8;  insert carperson(carid, carriderid) values (1, 1), (1, 2), (2, 2), (2, 3), (2, 4); select * carperson;  /* cardata: contains carid, color car painted, , person painted it, (fake) imageurl */  create table cardata ( dataid int(11) not null unique auto_increment, carid int(11) not null, color varchar(20), dataentrytime date,  painterid int(11) not null, imageurl varchar(255), primary key (dataid), foreign key (carid) references cars(carid), foreign key (painterid) references people(personid) ) engine=innodb default charset=utf8;  insert cardata(carid, color, dataentrytime, painterid, imageurl) values (1, "blue", "2013-1-1", 1, "http://www.bluecivic.com"), (1, "green", "2013-1-2", 3, "http://greencivic.com"), (2, "red", "2014-1-3", 4, "http://redford.com"), (2, "purple", "2014-1-4", 2, "http://purpleford.com"), (2, "black","2014-1-5", 3, "http://blackford.com"); select * cardata;  /* view having trouble */  create function p1() returns integer deterministic no sql return @p1; drop view carriderlist; create view carriderlist (select distinct cp.carid, cd.imageurl, dataentrytime, (             select group_concat(name order carid, personid = p1())from people p             left join carperson cp              on p.personid = cp.carpersonid              cp.carid in (                 select distinct(carid)                  carperson cp                 cp.carpersonid = p1()                 limit 1             )         )  people_concat carperson cp left join cardata cd on cd.carid = cp.carid cp.carriderid = p1()) order dataentrytime desc; select c.* (select @p1:=2 p) parm , carriderlist c;