Thanks Eric here is a sample select which brings back three rows without a distinct, and two rows with a distinct. All we want is to get the first row of the result set not the second or third. In Oracle PL/SQL I would've use WHERE ROWNUM = 1 . ROWNUM is an Oracle system variable and it would've only returned the first row of the rowset.

select ord_num,ord_date,qty,payterms from sales where ord_date = 'jun 15 92'

ord_num ord_date qty payterms -------------------- --------------------------- ------ ------------ P2121 Jun 15 1992 12:00AM 40 Net 30 P2121 Jun 15 1992 12:00AM 20 Net 30 P2121 Jun 15 1992 12:00AM 20 Net 30

select distinct ord_num,ord_date,qty,payterms from sales where ord_date = 'jun 15 92'

ord_num ord_date qty payterms -------------------- --------------------------- ------ ------------ P2121 Jun 15 1992 12:00AM 20 Net 30 P2121 Jun 15 1992 12:00AM 40 Net 30

(2 row(s) affected)

Is there a way in SQL 6.5 to only retrieve the first row of this query

Thanks again Eric. Paul

-- Anonymous, June 02, 1999

Answers

Paul,

What you want to do is to use the set rowcount before each query. You will set it to 1 before the query (which limits the result set to 1 row) and reset it to 0 after the query (which removes the limit on the number of rows in the result set). For example:

set rowcount 1

select ord_num, ord_date, qty, payterms from sales where ord_date = 'jun 15 92'

set rowcount 0

Hope this helps,

Eric

-- Anonymous, June 02, 1999