SQL Server 2005 Paging / LIMIT Query

Posted: July 14, 2009 in Database
Tags: , , , , ,

Salah satu fungsi yang tidak tersedia di SQL Server 2005 bila dibandingkan MySQL adalah fungsi ‘LIMIT‘.
Fungsi ini berguna untuk membatasi jumlah row yang diambil, dengan batasan awal dan jumlah yang ditentukan dengan parameter.
Contoh :

SELECT * FROM [someTable] LIMIT 1,2 (tidak bekerja di SQL Server 2005, ini hanya bekerja di MySQL)

One of the functions that not provided in SQL Server 2005 if it compared with MySQL is ‘LIMIT‘ function.
The function used for limiting amount of row being returned from a ‘select query’, by inputing parameters of beginning index as first cursor, and amount of rows to be returned.
e.g :

SELECT * FROM [someTable] LIMIT 1,2 (it doesn’t work in SQL Server 2005, it’s MySQL query)


Untuk melakukan paging di SQL Server 2005, seperti LIMIT pada MySQL, gunakan query berikut, menggunakan fungsi ROW_NUMBER(). (catatan : ketika diketik di query sheet, tulisan ROW_NUMBER() memang tidak akan berubah warna menjadi biru seperti kebanyakan fungsi-fungsi bawaan SQL SERVER, tapi ini berhasil) :

SELECT * FROM (SELECT [field1], [field2], [field3], ROW_NUMBER() OVER(ORDER BY [field1] ) AS rownum FROM [tableName] WHERE [conditional statement]) AS something WHERE rownum BETWEEN [start integer] AND [end integer]

ROW_NUMBER() menciptakan nomor yang berurutan, mengurutkan field yang disebutkan pada parameter fungsi OVER(), mengurutkannya dari nomor 1 sampai row terakhir dari field.

Despite that, we can still be able to do paging in SQL Server by using ‘ROW_NUMBER()’ function. (note : when we type ‘ROW_NUMBER()’ in a query sheet, the string won’t turn it’s color to blue or red like most of functions in SQL Server 2005, but it works).

SELECT * FROM (SELECT [field1], [field2], [field3], ROW_NUMBER() OVER(ORDER BY [field1] ) AS rownum FROM [tableName] WHERE [conditional statement]) AS something WHERE rownum BETWEEN [start integer] AND [end integer]

ROW_NUMBER() creates a series of number, indexing field mentioned in OVER() function as a parameter (in the query above, [field1] were set as the indexed field)

Comments
  1. hermanwhyd says:

    thanks y infonya,,,

    main” k hw-itech.blogspot.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s