Oracle - Select record with max date only

Question

I have the following query

SELECT b.BranchName AS "Branch Name",
             e.EmpNo AS "Employee Number",
             ed.Name AS "Employee Name",
             Max(e.visitdate) AS "Last Visiting Date",
             e.description AS "Visit Type"      
FROM   Employee e
INNER JOIN Branch b
             ON e.BranchNumber = b.BranchNumber
INNER JOIN EmpDetails ed
             ON e.EmpNo = ed.EmpNo
WHERE  b.BranchNumber BETWEEN '1' AND '20'
AND e.visitdate > '01-AUG-2021'
GROUP  BY b.BranchName,
              e.EmpNo,
              ed.Name,
              e.description
ORDER  BY b.BranchNumber,

When I run this query, it fetches me the following data -

Branch Name | Employee Number | Employee Name | Last Visiting Date      | Visit Type
A           | 1234            | Emma          | 11-Aug-2021 15:59       | XX
A           | 1234            | Emma          | 10-Aug-2021 12:04       | YY
A           | 1234            | Emma          | 10-Aug-2021 10:45       | BB

I am getting every unique record for that particular visit type but I only want the record of the latest visiting date . So , my output should only contain -

Branch Name | Employee Number | Employee Name | Last Visiting Date      | Visit Type
A           | 1234            | Emma          | 11-Aug-2021 15:59       | XX

This is just a sample dataset for demonstration purpose, my table has many employees, and I would want the record for their latest visit date along with the visit type for that latest date/time only. I tried rownum = 1 but it only fetches the topmost record whereas I would want it for every employee.

score 0 · Accepted Answer · answered Oct 26 '22 at 08:16

If I got it right, you could get rid of MAX function and use RANK analytic function which partitions data on each employee and sorts rows per visitdate in descending order, which means that row - that ranks as the highest - is the one you're looking for.

Something like this:

WITH
   your_query
   AS
      (SELECT b.branchname AS "Branch Name",
              e.empno AS "Employee Number",
              ed.name AS "Employee Name",
              e.visitdate AS "Last Visiting Date",
              e.description AS "Visit Type",
              RANK () OVER (PARTITION BY e.empno ORDER BY e.visitdate DESC) rnk
         FROM employee e
              INNER JOIN branch b ON e.branchnumber = b.branchnumber
              INNER JOIN empdetails ed ON e.empno = ed.empno
        WHERE     b.branchnumber BETWEEN '1' AND '20'
              AND e.visitdate > DATE '2021-08-01')
SELECT q.*
  FROM your_query q
 WHERE rnk = 1

Also, note that you should compare dates to dates, not dates to strings ('01-AUG-2021' is a string; you're relying on NLS settings and hoping that Oracle will recognize format you used). I used date literal (could've been to_date function with appropriate format model); I suggest you do it as well.

Oracle - Select record with max date only

1 Answers1