Thanks much for the feed back! 
I figured out how i needed to do this with the DISTINCT clause, be because I
needed to do SUM operations on a couple other columns




Michael K. Huber
Applications Development & AS/400 Operations
Microsoft Certified Professional
Phone:  (xxx) xxx-xxxx       FAX:  (xxx) xxx-xxxx      Mobile:  (xxx) xxx-xxxx
http://www/plymouthwater.com <http://www/plymouthwater.com> 
mailto:mhuber@p... <mailto:mhuber@p...> 





-----Original Message-----
From: trayce [mailto:trayce@j...]
Sent: Monday, February 05, 2001 10:21 AM
To: 'Huber, Mike '
Subject: RE: [sql_language] Syntax Question


Ignore the post about a cursor.
In fact, try to ignore cursors when you can.  They eat up a lot of resource,
they are generally slower than joins, harder to work with, and almost
anything can be done using joins rather than cursors.  Obviously not
everything, but most things I've tried to do, I can do with joins much much
much faster than cursors.  That aside,

You can do several things to emulate the FIRST in access.

1. SET ROWCOUNT x   

SET ROWCOUNT 1 --limits rowcount to 1 record
SELECT blah blah from blah  ORDER BY blah
SET ROWCOUNT 0 --removes limit

comments:  1.must remember to 'remove' the rowcount by resetting to 0
           2.must have an order by to guarantee you get the proper 'first'
               otherwise, the record you get may not be expected since it
               will go on the order of the primary key or phyical order
               if nothing else is given.
           3.since you have an order by, if you're trying to speed things
             up, this method isn't necessarily best because you have to
             wait for the sort to finish - which means you have to wait
             for the entire record set - if you're talking lots of records
             then this isn't the best way.

2. TOP
SELECT TOP x ...  or
SELECT TOP x PERCENT  ....


document from SQL Books on-line:
===================================================================
Limiting Result Sets Using TOP and PERCENT
The TOP clause limits the number of rows returned in the result set.

TOP n [PERCENT]

n specifies how many rows are returned. If PERCENT is not specified, n is
the number of rows to return. If PERCENT is specified, n is the percentage
of the result set rows to return:

TOP 120 /*Return the top 120 rows of the result set. */
TOP 15 PERCENT /* Return the top 15% of the result set. */.

If a SELECT statement that includes TOP also has an ORDER BY clause, the
rows to be returned are selected from the ordered result set. The entire
result set is built in the specified order and the top n rows in the ordered
result set are returned.

The other method of limiting the size of a result set is to execute a SET
ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from
TOP in these ways: 

The SET ROWCOUNT limit applies to building the rows in the result set after
an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement
is terminated when n rows have been selected from a set of values that has
been sorted according to specified ORDER BY classification.


The TOP clause applies to the single SELECT statement in which it is
specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT
statement is executed, such as SET ROWCOUNT 0 to turn the option off. 
===================================================================

3. OPTION (FAST n) - this is an optimizer hint.  haven't used it so I don't
really know much about it, but I think it will prove usefull.  If you're on
older versions of SQL, then it may need to be:
FASTFIRSTROW  instead of OPTION (Fast n).  FASTFIRSTROW still works with SQL
2000 for backward compatibility.

Hope these help and I wasn't too wordy.
Trayce Jordan






-----Original Message-----
From: Huber, Mike
To: sql language
Sent: 2001-02-05 07:51
Subject: [sql_language] Syntax Question

Could some one please enlighten me as the SQL equivalent of the MS
Access
FIRST?


------------------------------------------------------------------------
----
-----------
Michael K. Huber
Applications Development & AS/400 Operations
Microsoft Certified Professional
<http://www/plymouthwater.com>
<mailto:mhuber@p...>





$subst('Email.Unsub')