TOP Statement question...my correct e-mail address. (Disregard netcom addr)
Lee J. Weiner
Hello!
ARGH! HELP!
I need something like Access's's's's "TOP" statement,
"SELECT TOP 10 from foo",
in Transact-SQL. Is this possible? % too?
Any response would be most appreciated.
-Lee Weiner
lwe...@mico.mt.com
----------------------------------
Sig? I'm too old for a sig.
----------------------------------
James Sharier Jr.
Lee J. Weiner <lwe...@mico.mt.com> wrote in article
<3363E7...@mico.mt.com>...
> I need something like Access's's's's "TOP" statement,
> "SELECT TOP 10 from foo",
> in Transact-SQL. Is this possible? % too?
Hi Lee,
SET ROWCOUNT x
<your select statement>
You can also use this to get the last x if you have a column that in
chronological order and use the ORDER BY <col> DESC.
HTH
--
_
-------------------ooO-( )-Ooo---------------------------------
James Sharier Jr. <>< (0 0) (512)602-5193 james....@amd.com
~~~
Stuart Hemming
In article <3363E7...@mico.mt.com>, lwe...@mico.mt.com (Lee J.
Weiner) wrote:
> Hello!
> ARGH! HELP!
> I need something like Access's's's's "TOP" statement,
> "SELECT TOP 10 from foo",
> in Transact-SQL. Is this possible? % too?
> Any response would be most appreciated.
set rowcount 5
select * from tablename
order by ranking_field
-------------------------------------------------------------------
These are *my* opinions. Please don't make the mistake of believing
anybody else is stupid enough to think like this!
-------------------------------------------------------------------
Stuart Hemming | shem...@cix.compulink.co.uk
The Data Base (Nottm) Ltd | stu...@database.co.uk
11 Glaisdale Dr East, Bilborough | Tel: + 44 (0) 115 971 2000
Nottingham NG8 4BU UK | Fax: + 44 (0) 115 929 4668
-------------------------------------------------------------------
Ted Knijff
>In article <3363E7...@mico.mt.com>, lwe...@mico.mt.com (Lee J.
>Weiner) wrote:
>
>> Hello!
>> ARGH! HELP!
>> I need something like Access's's's's "TOP" statement,
>> "SELECT TOP 10 from foo",
>> in Transact-SQL. Is this possible? % too?
>> Any response would be most appreciated.
>
Hi,
I don't know what exactly you are trying to achieve. In MS-Access, as
well as in xBase Files (Tables), you have something like a record
count (RECNO() in xBase). Unless you specifically add this to your
SQL-Tables and use it as a key, there is no easy, fast way to access a
numbered record (=row). The solution I use is to add an IDENTITY
datatype in MS-SQL 6.5 (also called serial datatype) which I then use
to uniquely identify a row. MS-SQL handles this all by itself. You
define it as an INTEGER (or TINYINT, SMALLINT). It survives over
updates (unlike TIMESTAMP) and is fast. It also allows you to browse
(horrors !) your SQL-Tables forwards and backwards, got to top or
bottom of the table (even bigger horrors !). Don't use it if you do a
lot of deletes in the table (the biggest horror!).
Ted Knijff
Ted Knijff
Ell
: >In article <3363E7...@mico.mt.com>, lwe...@mico.mt.com (Lee J.
: >Weiner) wrote:
: >
: >> Hello!
: >> ARGH! HELP!
: >> I need something like Access's's's's "TOP" statement,
: >> "SELECT TOP 10 from foo",
: >> in Transact-SQL. Is this possible? % too?
: >> Any response would be most appreciated.
: >
There is a 'set count X' statement in tsql, which can limit the number of
rows returned to X. 'set count 0' sets things to default.
Elliott
Steve Jorgensen
IMPORTANT additional information!
Note that SET ROWCOUNT stays in effect for the current connection until
it is terminated, til the end of the surrounding SP, or until SET ROWCOUNT
0 is issued. I recommend issuing SET ROWCOUNT 0 immediately after
the query or, if error handling is required, after storing @@ERROR into a
local variable.
SET ROWCOUNT x
<your select statement>
SELECT @Error = @@ERROR
SET ROWCOUNT 0
IF @Error ...