| Date: | Fri, 17 Jun 2005 18:58:14 -0400 |
| From: | Jesse Vincent <jesse [...] bestpractical.com> |
| To: | bug-dbix-searchbuilder [...] rt.cpan.org |
| Subject: | [fwd] [Rt-devel] Re: fyi - mssql select top 10 next 10 (fwd) (from: joby@u.washington.edu) |
--
Return-Path: <rt-devel-bounces@lists.bestpractical.com>
X-Original-To: jesse@bestpractical.com
Delivered-To: j@diesel.bestpractical.com
Received: from localhost.localdomain (localhost.localdomain [127.0.0.1])
by diesel.bestpractical.com (Postfix) with ESMTP id E83C84D826B;
Fri, 17 Jun 2005 17:18:48 -0400 (EDT)
X-Original-To: rt-devel@lists.bestpractical.com
Delivered-To: rt-devel@diesel.bestpractical.com
X-Greylist: delayed 2250 seconds by postgrey-1.21 at diesel;
Fri, 17 Jun 2005 17:18:43 EDT
Received: from mxout4.cac.washington.edu (mxout4.cac.washington.edu
[140.142.33.19])
(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
(No client certificate requested)
by diesel.bestpractical.com (Postfix) with ESMTP id DE7EA4D801B
for <rt-devel@lists.bestpractical.com>;
Fri, 17 Jun 2005 17:18:43 -0400 (EDT)
Received: from smtp.washington.edu (smtp.washington.edu [140.142.32.139])
by mxout4.cac.washington.edu (8.13.4+UW05.04/8.13.4+UW05.05) with ESMTP
id j5HKfBkK001328
(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK)
for <rt-devel@lists.bestpractical.com>; Fri, 17 Jun 2005 13:41:12 -0700
Received: from [128.95.135.62] (bear.cac.washington.edu [128.95.135.62])
(authenticated authid=joby)
by smtp.washington.edu (8.13.4+UW05.04/8.13.4+UW05.05) with ESMTP id
j5HKfB05027256
(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT)
for <rt-devel@lists.bestpractical.com>; Fri, 17 Jun 2005 13:41:11 -0700
Message-ID: <42B33567.509@u.washington.edu>
Date: Fri, 17 Jun 2005 13:41:11 -0700
From: Joby Walker <joby@u.washington.edu>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050421)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: rt-devel@lists.bestpractical.com
References: <Pine.A41.4.56.0506171036530.104550@nazca.cac.washington.edu>
In-Reply-To: <Pine.A41.4.56.0506171036530.104550@nazca.cac.washington.edu>
X-Enigmail-Version: 0.91.0.0
OpenPGP: id=08647FCF;
url=https://staff.washington.edu/joby/joby-u-pub.asc
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Subject: [Rt-devel] Re: fyi - mssql select top 10 next 10 (fwd)
X-BeenThere: rt-devel@lists.bestpractical.com
X-Mailman-Version: 2.1.5
Precedence: list
List-Id: rt-devel.lists.bestpractical.com
List-Unsubscribe: <http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel>,
<mailto:rt-devel-request@lists.bestpractical.com?subject=unsubscribe>
List-Archive: <http://lists.bestpractical.com/pipermail/rt-devel>
List-Post: <mailto:rt-devel@lists.bestpractical.com>
List-Help: <mailto:rt-devel-request@lists.bestpractical.com?subject=help>
List-Subscribe: <http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel>,
<mailto:rt-devel-request@lists.bestpractical.com?subject=subscribe>
Sender: rt-devel-bounces@lists.bestpractical.com
Errors-To: rt-devel-bounces@lists.bestpractical.com
X-Spam-Checker-Version: SpamAssassin 3.0.3 (2005-04-27) on
diesel.bestpractical.com
X-Spam-Level:
X-Spam-Status: No, score=-2.6 required=7.0 tests=BAYES_00 autolearn=ham
version=3.0.3
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
This is a follow up on the sybase/mssql support question I asked a few
weeks ago. Below is an example of how to get a limit/offset query
working in mssql using sub-selects.
But then we may have convinced our DB As that PgSQL will probably meet
our enterprise needs, so for now we're not pushing for this support.
Joby Walker
ITI SSG, University of Washington
- --
PGP key: https://staff.washington.edu/joby/joby-u-pub.asc
-
--------------------------------------------------------------------------------------
Show quoted text
>>
>>http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850&lngWId=5
>>
>>stumbled on this when looking for something else -- request tracker
>>question a couple weeks ago --
>>
>>The following query will emulate a
>>
>>SELECT emp_id,lname,fname FROM employee LIMIT 20,10
>>
>>That says, give me 10 records starting at record 21. So what will be
>>returned are rows 21-30. This is used heavily in web-based apps so you can
>>do recordset paging.
>>
>>Here is what you can do in MS SQL to emulate it (this runs on the PUBS
>>db):
>>
>>select * from (
>>select top 10 emp_id,lname,fname from (
>> select top 30 emp_id,lname,fname
>> from employee
>> order by lname asc
>>) as newtbl order by lname desc
>>) as newtbl2 order by lname asc
>>
>>The main thing to remember is to deal with all your ORDER BY clauses and
>>also to use the inner TOP as the sum of what you are looking for. In this
>>example - 30 since you start at 20 and want 10 rows (20+10=30).
>>
>>Hope this helps you as it has helped me improve performance greatly on my
>>web-based apps since I no longer have to transfer loads of data across the
>>wire to get just the 10 results I display on a page.
>>
>>Don't even think about using the internal ADO paging stuff if you have a
>>lot of records to deal with, it will kill your performance as it must pull
>>the entire recordset down to the local machine before it can do its work.
>>Why bring down 20,000 records if you only want to display 10?
>>
>>Enjoy!
>>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCszVngA0gpghkf88RAhpKAKDBRJrl2jV6awLXMY6EX6AFxJoTgACgiQRG
lbmbBnVyks75CR9vmddaJZg=
=5ssB
-----END PGP SIGNATURE-----
Show quoted text_______________________________________________
Rt-devel mailing list
Rt-devel@lists.bestpractical.com
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel