SQL: How to use functions/operations in a where condition

Question

select * from mytable
where date = max(date)

This does not work. I hope my motive is clear. I know I can come around this problem by using sub-queries but is it possible to it without sub-query?

are you trying to get the latest row of mytable? Or what are you exactly trying to do? — PRIVATE ACCOUNT, Jan 10 '22 at 12:38
Does this answer your question? [How do I do top 1 in Oracle?](https://stackoverflow.com/questions/3451534/how-do-i-do-top-1-in-oracle) (Be sure to look at all the answers; some address the issue of "how to do it without a subquery" and "what if rows are not unique this way and I want all of them".) — Jeroen Mostert, Jan 10 '22 at 12:40

score 0 · Answer 1 · answered Jan 10 '22 at 12:40

Subquery it is, unless you create your own function which can then be used directly in the WHERE clause. Otherwise, I don't think you can do that.

For example:

SQL> select ename, hiredate from emp order by hiredate desc;

ENAME      HIREDATE
---------- ----------
ADAMS      12.01.1983       --> that's the one we want
SCOTT      09.12.1982
MILLER     23.01.1982
FORD       03.12.1981
JAMES      03.12.1981
<snip>

The way you already know can be improved as following (why is it improved? Because it scans the table only once; your way, I presume, does it twice - once in a subquery (to find the MAX date value), and then once again in the main query):

SQL> with temp as
  2    (select e.*,
  3            rank() over (order by e.hiredate desc) rnk
  4     from emp e
  5    )
  6  select ename, hiredate
  7  from temp
  8  where rnk = 1;

ENAME      HIREDATE
---------- ----------
ADAMS      12.01.1983

(Option which isn't that good):

SQL> select ename, hiredate
  2  from emp
  3  where hiredate = (select max(hiredate) from emp);

ENAME      HIREDATE
---------- ----------
ADAMS      12.01.1983

SQL>

Or, with your own function:

SQL> create or replace function f_maxhire return date is
  2    retval date;
  3  begin
  4    select max(hiredate) into retval from emp;
  5    return retval;
  6  end;
  7  /

Function created.

SQL> select ename, hiredate
  2  from emp
  3  where hiredate = f_maxhire;

ENAME      HIREDATE
---------- ----------
ADAMS      12.01.1983

SQL>

SQL: How to use functions/operations in a where condition

1 Answers1