Select longest duration time (data type field) and client name SQL

Question

I have these tables:

CREATE TABLE Client 
(
    client_id NUMBER(10) NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL, 
    address VARCHAR(50) NOT NULL
);

CREATE TABLE Projects 
(
    project_id NUMBER(10) NOT NULL PRIMARY KEY,
    project_name VARCHAR(200) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    client_id NUMBER(10) NOT NULL,
    CONSTRAINT client_fk 
        FOREIGN KEY (client_id) REFERENCES Client(client_id)
);
 
INSERT INTO Client (client_id, name, address) 
VALUES (1, 'Joe Doe', '11 Henry Smith St.Chelsea, MA 02150');
         
INSERT INTO Client (client_id, name, address) 
VALUES (2, 'James Doe', '74 East Sierra Ave. Batavia, OH 45103');
         
INSERT INTO Projects (project_id, project_name, start_date, end_date, client_id) 
VALUES (1, 'YYY', TO_DATE('2020/12/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),
        TO_DATE('2020/12/30 21:02:44', 'yyyy/mm/dd hh24:mi:ss'), 1);

INSERT INTO Projects (project_id, project_name, start_date, end_date, client_id) 
VALUES (2, 'XXX', TO_DATE('2020/11/01 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),
        TO_DATE('2020/12/30 21:02:44', 'yyyy/mm/dd hh24:mi:ss'), 2);
     

I want to select project with maximum duration time together with employee name.

I wrote this SQL statement:

SELECT 
    p.project_name, c.name, 
FROM
    Projects p, Client c
WHERE
    p.client_id = c.client_id 
    AND max_time = (SELECT MAX(p.end_date - p.start_date) FROM Projects p);
     

But I get an error

ORA-00936: missing expression

Here's the code: http://sqlfiddle.com/#!4/00720/1

Answer 1

You have glitches in column names in the fiddle, and an unwanted trailing comma after c.name.

Consider:

SELECT p.project_name, c.name
FROM Projects p
INNER JOIN Client c ON p.client_id = c.client_id 
WHERE (p.end_date - p.start_date)  = (
      SELECT MAX(end_date - start_date) FROM Projects
);

In Oracle 12 or higher you can also use a FETCH clause, as suggested by Gordon Linoff. I would recommend WITH TIES to make the query equivalent to your original code:

SELECT p.project_name, c.name
FROM Projects p
INNER JOIN Client c ON p.client_id = c.client_id 
ORDER BY p.end_date - p.start_date DESC
FETCH FIRST ROW WITH TIES

In older versions, you can use RANK() instead:

SELECT *
FROM (
    SELECT p.project_name, c.name,
        RANK() OVER(ORDER BY p.end_date - p.start_date DESC) as rn
    FROM Projects p
    INNER JOIN Client c ON p.client_id = c.client_id 
) t
WHERE rn = 1