Software

Ask something
Discussion Locked

MySQL LIMIT in SQL Server

0 helpful
10 replies

by just_chilin 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Is there a way to mimick the MySQL LIMIT function in MS SQL Server?

10 replies

by just_chilin

Acually SET ROWCOUNT will return the first set of records. e.g.
SET ROWCOUNT 100
SELECT * FROM Customers
will return the first 100 Customers. But in MySQL

SELECT * FROM Customers LIMIT 100 - will return the first 100

SELECT * FROM Customers LIMIT 101,200 - will return from record # 101 to 200.

Is this possible in SQL Server or Oracle?

by sanatorium

You could use a table variable with an identity column:

DECLARE @tbl (
intRowId int IDENTITY (1,1),
chrCustomerName varchar(50),
...
)

INSERT INTO @tbl
SELECT * FROM Customers

SELECT * FROM @tbl WHERE (intRowId > 100) AND (intRowId < 200)

It's a bit of a hack, but it will do what you're looking for...

Oh, and SET ROWCOUNT x is the same as SELECT TOP x, and you don't need to turn TOP x off when you're done...

-C

by randall.gibson

In Oracle you can use the pseudo-column rownum. So the query would be:

SELECT * FROM Customers where rownum < 100;

SELECT * FROM Customers where rownum > 100 and rownum < 201;
or
SELECT * FROM Customers where rownum BETWEEN 101 and 200;

by sholodak

Let's say you want to select a window of 10 records out of a table.

SELECT TOP 10 *
FROM [Table]
WHERE [Primary_Key] NOT IN
(SELECT TOP ### [Primary_Key]
FROM [Table]
ORDER BY [Sort_Field]))
ORDER BY [Sort_Field]

Replace the ### with a number. If you set ### to 0, you'll get just the top 10 rows. If you set ### to 10, you'll get rows 11-20, set to 20 for rows 21-30, etc.

Scott

by arielglikmanwww

If the query you want to page is "SELECT * FROM SALES", probably exist another solutions. But if your query is a little more complicated, this is a good option. This script may be a quite slow but it's generic.

This example use pages of 20 rows, and take de second page of "SELECT * FROM SALES".

SELECT Identity(int, 1, 1) AS row_number, temp_table_paging.* INTO #TEMP_PAGING_TABLE FROM ( SELECT * FROM SALES ) as temp_table_paging

SELECT * FROM #TEMP_PAGING_TABLE WHERE row_number >= 20 AND row_number < 39

SELECT max(row_number)as cantidad FROM #TEMP_PAGING_TABLE

DROP TABLE #TEMP_PAGING_TABLE

by r-deo

Is it true that DB2 would append orphaned detail records to the previously read header record? Is there a way for DB2 to check for key fields defined in header and detail files before attaching detail records to a header record?

This discussion is closed to new replies.