how to get last record of table in sql

Question

I have three entries in the attendance table.But I need to print the last entry of the id from the attendance table.I calculated total duration in separate query and printing list in separate query.Anyone please help me to resolve this.

query to print total duration:

select SEC_TO_TIME(sum(case when a.endtime  IS NULL 
                         then time_to_sec(TIMEDIFF(NOW(),a.starttime))  
                         else time_to_sec(a.duration) 
                       end)
                   ) as duration
from attendance a
left join staff s on a.staffid=s.id
left join company c on a.companyid=c.companyid 
where DATE_FORMAT(a.createdon, '%Y/%m/%d') = DATE_FORMAT('2017-04-04', '%Y/%m/%d') and 
(a.employeecode ='A101' OR 'A101'='') and
(a.companyid=0 OR 0=0)
group by a.employeecode,a.companyid order by a.id desc

Query to print the details:

select a.id, 
       a.starttime, 
       a.endtime,
       a.startlocation, 
       a.endlocation, 
       a.duration, 
       a.companyid, 
       a.employeecode, 
       a.staffid, 
       a.createdon, 
       a.createdby,
       a.lastmodifiedon,    
       a.reason, 
       a.comments,
       s.name as staffName, 
       c.company_name as companyName 
from attendance a
left joinstaff s on a.staffid=s.id 
left join company c on a.companyid=c.companyid 
where DATE_FORMAT(a.created_on, '%Y/%m/%d') = DATE_FORMAT('2017-04-04', '%Y/%m/%d') and 
(a.employeecode ='A101' OR 'A101'='') and 
(a.company_id=0 OR 0=0)
group by a.employeecode,a.companyid order by a.id desc

Possible duplicate of [How to select the last record of a table in SQL?](http://stackoverflow.com/questions/5191503/how-to-select-the-last-record-of-a-table-in-sql) — mxr7350, Apr 04 '17 at 11:52

score 0 · Answer 1 · answered Apr 05 '17 at 12:21

I got output by setting the endtime as null.

`select SEC_TO_TIME(sum(case when a.endtime IS NULL then  time_to_sec(TIMEDIFF(NOW(),a.starttime)) else time_to_sec(a.duration) end)) 
from attendance a left join staff s on a.staffid=s.id
left join company c on a.companyid=c.companyid 
where DATE_FORMAT(a.createdon, '%Y/%m/%d') = DATE_FORMAT('', '%Y/%m/%d') and 
(a.employeecode ='' OR ''='') and 
(a.companyid=0 OR 0=0)
group by a.employeecode,a.companyid order by a.id desc`

how to get last record of table in sql

1 Answers1