What will usually work is "group by" but you must be aware of many things:

o group by does not have to sort. In 7.3 it almost always does but as the versions go on, it does not. Partitioning, Index Organized tables, better optimizer, and so on make this the case.

o group by does a binary sort, if you are using NLS this might result in data being ordered differently then if you use an order by.

It might look like this:

select *
from ( select c1, c2, c3, cola, colb
from T
group by cola, colb, c1, c2, c3 )
where rownum = 1;


If in fact, cola+colb is unique, you can:

select *
from ( select min(c1), min(c2), min(c3), cola, colb
from T
group by cola, colb )
where rownum = 1;

for example:

1 select *
2 from ( select ename, min(empno), min(deptno)
3 from emp
4 group by ename )
5* where rownum = 1
ask_tom@OSI1.WORLD> /

ENAME MIN(EMPNO) MIN(DEPTNO)
---------- ---------- -----------
ADAMS 7876 20

1 row selected.


So, just to make sure I've caveated this -- make sure to test this on your data if you use a group by (and to convert it when you goto 8i). Here is an example of a group by that does not sort the way you expect:
SQL> create table emp as select * from scott.emp where rownum <= 5;
Table created.

SQL> create index emp_idx on emp(job,ename);
Index created.

SQL> select ename, job from emp where ename >chr(0) and job >chr(0)
2 group by ename, job ;

ENAME JOB
---------- ---------
smith CLERK
jones MANAGER
allen SALESMAN
martin SALESMAN
ward SALESMAN

Since group by ename, job is the same as group by job, ename -- it just used the index to do the work here and blew off the sort -- so beware.


Another option that always works would be:

ask_tom@OSI1.WORLD> select ename, empno, deptno
2 from emp a
3 where ename = ( select min(ename)
4 from emp )
5 and deptno = ( select min(deptno)
6 from emp b
7 where b.ename = a.ename )
8 and rownum = 1
9 /

ENAME EMPNO DEPTNO
---------- ---------- ----------
ADAMS 7876 20


1 row selected.


Where ename represents COLA and deptno represents COLB. We find the smallest (or largest) ename and for that ename we find the smallest deptno for that ename. We find the first row having that smallest ename, deptno.