0
select 
    max((date '2019-07-01' - applicant.applicantbirthdate) / 365 as age, 
    applicant.applicantfirstname, 
    applicant.applicantlastname, 
    office.officename
from 
    applicant
inner join 
    office on office.officeid = applicant.officeid
group by 
    applicant.applicantfirstname, applicant.applicantlastname, 
    office.officename;

I'm not really sure what to do here. I'm suppose to pick the oldest user and list their office. but this shows the oldest user for all offices. how can I just only show the oldest person out of all the offices

MT0
  • 143,790
  • 11
  • 59
  • 117
DrVernCarson
  • 1
  • 1
  • Please, provide sample data and desired output for it (in text format or as `insert` statements). – astentx Feb 21 '22 at 06:47
  • Does this answer your question? [Taking the record with the max date](https://stackoverflow.com/questions/8898020/taking-the-record-with-the-max-date) – astentx Feb 21 '22 at 06:49

3 Answers3

0

This example is based on Scott's sample schema whose tables simulate what you have.

SQL>   SELECT ename, hiredate
  2      FROM emp
  3  ORDER BY hiredate DESC;

ENAME      HIREDATE
---------- ----------
ADAMS      12.01.1983       --> Adams has the MAX hiredate; you want his data
SCOTT      09.12.1982
MILLER     23.01.1982
FORD       03.12.1981
JAMES      03.12.1981
KING       17.11.1981
MARTIN     28.09.1981
TURNER     08.09.1981
CLARK      09.06.1981
BLAKE      01.05.1981
JONES      02.04.1981
WARD       22.02.1981
ALLEN      20.02.1981
SMITH      17.12.1980

14 rows selected.

Using a CTE which includes the RANK analytic function that "sorts" rows by hiredate in descending order (so that people - who have the MAX hiredate - rank as "highest")), in final query you just fetch those whose rank = 1:

SQL> WITH
  2     temp
  3     AS
  4        (SELECT e.ename,
  5                d.dname,
  6                e.hiredate,
  7                RANK () OVER (ORDER BY e.hiredate DESC) rn
  8           FROM emp e JOIN dept d ON e.deptno = d.deptno)
  9  SELECT ename, dname, ROUND ((SYSDATE - hiredate) / 365) age
 10    FROM temp
 11   WHERE rn = 1;

ENAME      DNAME                 AGE
---------- -------------- ----------
ADAMS      RESEARCH               39

SQL>

Applied to your query:

WITH
   temp
   AS
      (SELECT a.applicantfirstname,
              a.applicantlastname,
              o.officename,
              a.birthdate,
              RANK () OVER (ORDER BY a.birthdate DESC) rn
         FROM applicant a JOIN office o ON o.officeid = a.officeid)
SELECT applicantfirstname,
       applicantlastname,
       officename,
       ROUND ((SYSDATE - birthdate) / 365) AS age
  FROM temp
 WHERE rn = 1;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

#The below code should work. I recommend to go with RANK over if its large dataset but if its small dataset the below code works fine as well.

select applicant.applicantfirstname, applicant.applicantlastname, office.officename, max((date '2019-07-01' - applicant.applicantbirthdate) / 365 as age
FROM
         applicant
    INNER JOIN office ON office.officeid = applicant.officeid
WHERE
    applicant.applicantbirthdate IN (
        SELECT MIN(applicant.applicantbirthdate) FROM applicant);
Developer
  • 3
  • 3
-1

Try the below one. There are two things missing in your query.

  1. We should list them based on descending order. So putting ORDER BY age DESC would ensure it.
  2. We need to get the oldest candidate in terms of age. So LIMIT 1 would ensure that we would get only one record.
select (date '2019-07-01' - applicant.applicantbirthdate)/365 as age, applicant.applicantfirstname, applicant.applicantlastname, office.officename
from applicant
inner join office on office.officeid = applicant.officeid
group by applicant.applicantfirstname, applicant.applicantlastname, office.officename
order by age desc
limit 1;
KdPisda
  • 210
  • 6
  • 14