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– PRIVATE ACCOUNT2022-01-10 12:38:56 +00:00Commented Jan 10, 2022 at 12:38
- 1Does this answer your question? 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– Jeroen Mostert2022-01-10 12:40:26 +00:00Commented Jan 10, 2022 at 12:40
Add a comment |
1 Answer
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> 