Returning first, last rows in a group by SQL statement 1

[mike777]

In MS Access, there are FIRST and LAST group-by functions where you can return the last value (via natural order, I presume) of columns where other columns are grouped on.
I see MAX and MIN here in Oracle, but nothing like FIRST or LAST. Is there an equivalent?
I have a table that provides no way of determining via the MAX and MIN functions which record is first or last.
Usually, I would say

select m.acct_num,m.comment,m.comment_date
  from mytable m,
       (select m1.acct_num,max(comment_date) MaxDate
          from mytable m1
      group by acct_num) sql1
 where m.acct_num=sql1.acct_num
   and m.comment_date=sql1.MaxDate

in order to retrieve the most recent data.
I cannot do this in this case and must rely on natural order. Do you have any suggestions?
Also, if you have any ideas about whether the way I determine the most recent data using MIN and MAX is good or not, I would appreciate that as well.
Many thanks.
-Mike Kemp

 

[mike777]

P.S. I've also experimented with using the ROWNUM pseudocolumn, but to no avail. Maybe I don't know how to use it, but it appears to me to return intelligible numbers when doing a simple query on a table, but in my real-life case, I can't make heads or tails of the numbers it's giving me. It is a multiple-table, correlated sub-query SQL statement, and the first value of ROWNUM is like 12873 or something.
Thanks.

 

[jee]

There are FIRST_VALUE and LAST_VALUE analytical functions that you can use (new in Oracle 8i). One drawback - you can't use these functions within PL/SQL unless you use DYNAMIC SQL. Here is an example of FIRST_VALUE, lifted directly from the Oracle SQL Reference Guide:

The following example selects, for each employee in Department 20, the name of the employee with the highest salary.

SELECT deptno, ename, sal, FIRST_VALUE(ename)
OVER (ORDER BY sal DESC ROWS UNBOUNDED PRECEDING) AS rich_emp
FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno);

DEPTNO ENAME SAL RICH_EMP
---------- ---------- ---------- ----------
20 SCOTT 3000 SCOTT
20 FORD 3000 SCOTT
20 JONES 2975 SCOTT
20 ADAMS 1100 SCOTT
20 SMITH 800 SCOTT

The example illustrates the nondeterministic nature of the FIRST_VALUE function. Scott and Ford have the same salary, so are in adjacent rows. Scott appears first because the rows returned by the subquery are ordered by empno. However, if the rows returned by the subquery are ordered by empno in descending order, as in the next example, the function returns a different value:

SELECT deptno, ename, sal, FIRST_VALUE(ename)
OVER (ORDER BY sal DESC ROWS UNBOUNDED PRECEDING) AS fv
FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno desc);

DEPTNO ENAME SAL FV
---------- ---------- ---------- ----------
20 FORD 3000 FORD
20 SCOTT 3000 FORD
20 JONES 2975 FORD
20 ADAMS 1100 FORD
20 SMITH 800 FORD

The following example shows how to make the FIRST_VALUE function deterministic by ordering on a unique key.

SELECT deptno, ename, sal, hiredate, FIRST_VALUE(ename)
OVER (ORDER BY sal DESC, hiredate ROWS UNBOUNDED PRECEDING) AS fv
FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno desc);

DEPTNO ENAME SAL HIREDATE FV
---------- ---------- ---------- --------- ----------
20 FORD 3000 03-DEC-81 FORD
20 SCOTT 3000 19-APR-87 FORD
20 JONES 2975 02-APR-81 FORD
20 ADAMS 1100 23-MAY-87 FORD
20 SMITH 800 17-DEC-80 FORD

 

[mike777]

I say, old chap, spot on!
Many thanks.
-Mike