Hi
I want a query which will return first n records from a table in Sybase.
I do not want to use the SET Rowcount option. Can anybody suggest me the
solution for the same.


Thanks and Regards
-Abhi

On Tue, 04 Nov 2003 08:59:14 -0800, Abhi wrote:

> Hi
> I want a query which will return first n records from a table in Sybase.
> I do not want to use the SET Rowcount option.

Why?

> Can anybody suggest me the solution for the same.

If you control the client you could fetch the appropriate number of rows
and then cancel the request, but that's not going to be very efficient.

I could think of a few other possible ideas, but they're all pretty ugly.

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler@peppler.org http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html

Abhi wrote:

> I want a query which will return first n records from a table in Sybase.

Based on any specific criteria, or do you just want to
limit it to ANY n rows?


Larry Coon
University of California

I completely agree with the comments made by Michael and Larry. That said,
you might be able to write code to do what you want based upon the SQL
below. It returns the "first" N records by name from sysobjects. Note, in
general, some possible problems.

(1) If there are "duplicate keys" then (N-1), (N+1), etc. rows might be
returned instead of N.
(2) If "first" depends upon multiple columns (and especially if the column
datatypes differ) then the query can be quite ugly. Actually, I can see a
generalization if all of the columns are [var]char (use concatenation).
Otherwise I don't.
(3) If the table is large and there is no useful index performance could be
terrible.

select (o.name)

from sysobjects o

having N > (select count (o2.name)

from sysobjects o2

where o2.name < o.name)

order by o.name



"Abhi" <getabhijit@indiatimes.com> wrote in message
news:67db1745.0311040859.28f0b3d1@posting.google.c om...
> Hi
> I want a query which will return first n records from a table in
Sybase.
> I do not want to use the SET Rowcount option. Can anybody suggest me the
> solution for the same.
>
>
> Thanks and Regards
> -Abhi

"Abhi" <getabhijit@indiatimes.com> wrote in message
news:67db1745.0311040859.28f0b3d1@posting.google.c om...
> Hi
> I want a query which will return first n records from a table
in Sybase.
> I do not want to use the SET Rowcount option. Can anybody suggest me
the
> solution for the same.
>
>
> Thanks and Regards
> -Abhi

I also think you should simply use 'set rowcount'. But if you don't
want to do that (tell us why...) this is an alternative:

select row_number = identity(9), * into #t from your_table order by
<your-ordering-criterium>

select * from #t where row_number <= N -- N = a number defined by
you


Note the the whole concept of 'the first N rows' is rather shaky
without defining the what ordering you have in mind. Hence, you should
provide an 'order by' to define this.

Also note that this is not advisable when the table in question is
large.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of "Tips, Tricks & Recipes for Sybase ASE" and
"The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------