GROUPS ISSUE
Posted: Thu Jan 31, 2008 9:34 am
Code: Select all
SELECT
md5(concat(st.userid,st.scormid,st.scoid)) as rid,
u.firstname,
u.lastname,
sc.name,
sco.title,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(st.element,'::::',`value` order by st.element SEPARATOR '^^^^'),'^^^^',1),'::::',-1) as `status`,
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(st.element,'::::',`value` order by st.element SEPARATOR '^^^^'),'^^^^',2),'::::',-1),'t0',1) as `time`,
FROM_UNIXTIME(MIN(st.timemodified),'%d/%m/%Y') As datefirstaccess,
FROM_UNIXTIME(MAX(st.timemodified),'%d/%m/%Y') As datelastaccess
FROM mdl_scorm_scoes_track st inner join mdl_user u
on st.userid = u.id
inner join mdl_scorm sc
on st.scormid = sc.id
inner join mdl_scorm_scoes sco
on st.scoid = sco.id
where datelastaccess > UNIX_TIMESTAMP('25-09-1978')
and datelastaccess < UNIX_TIMESTAMP('25-09-1978')
group by ridAnyone know where i'm going wrong? Thanks