0

Hello everyone I have wrote a query which will give me employee in and out time for a range of time, as my tables have many in and out time I wanted to get only last in or out time based on timestamp here is query for this

select
fullname,direction,to_char(((TO_DATE('19700101','yyyymmdd') + (tstamp/24/60/60) + 6/24)), 'dd-mm-yyyy  hh12:mi:ss PM') as Time,
gate,lane,employment.employeeid,NATIONALID,departmentname,designation.designationname
from eofficeuat.entrylog_cpa
join eofficeuat.employee on entrylog_cpa.hrrecordid=employee.hrrecordid
join eofficeuat.employment on employee.hrrecordid=employment.hrrecordid
join eofficeuat.designation on employment.designationid=designation.designationid
join eofficeuat.department on employment.departmentid=department.departmentid
where department.departmentname = 'SECURITY'
and tstamp >= 1568764800 and tstamp < (select (sysdate - date '1970-01-01') * 86400000 from dual)
/*and tstamp in (select max(tstamp) from eofficeuat.entrylog_cpa)*/
/*and rownum >= (select count(tstamp) from eofficeuat.entrylog_cpa)*/
and fullname='A.K.M.TOWHID SARKER'
order by fullname asc;

this query give this result 

 full name            direction   time
 ---------            ---------   ----
 A.K.M.TOWHID SARKER  out         18-09-2019  01:41:35 PM
 A.K.M.TOWHID SARKER  out         18-09-2019  01:29:08 PM

but I only want last row from this result. can you please help how can i do this?

GMB
  • 188,822
  • 23
  • 52
  • 100
mdkamrul
  • 214
  • 1
  • 10

1 Answers1

0

You can wrap your query and use ROW_NUM():

SELECT *
FROM (
    -- your query with an ORDER BY clause
) x WHERE ROWNUM = 1

Please note that this selects the first row in the resultset. If you want the last row, you would need to change your ORDER BY clause to sort the opposite way in the first place.

If you are running Oracle 12c or higher, you don't need to wrap the query, you can use a FETCH FIRST ROW ONLY clause.

SELECT ...
FROM ...
ORDER BY ...
FETCH FIRST ROW ONLY
GMB
  • 188,822
  • 23
  • 52
  • 100
  • Where ROWNUM=1 only showing first row from this result, I wanted get last row – mdkamrul Sep 18 '19 at 09:43
  • @mdkamrul: yes, see the note in my answer. you just need to reverse the sorting order, so `order by fullname desc` instead of `order by fullname asc`. – GMB Sep 18 '19 at 09:58
  • 1
    Note: since you are using 19c, you could use `FETCH FIRST ROW ONLY` instead of `ROWNUM` (still sorting by *descending* name) – GMB Sep 18 '19 at 10:00
  • when i run Fetch first row only then I am getting the error "ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 16 Column: 1" – mdkamrul Sep 18 '19 at 10:58