General discussion

Locked

MySQL LIMIT in SQL Server

By just_chilin ·
Is there a way to mimick the MySQL LIMIT function in MS SQL Server?

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

been a while but it's

by Tony Hopkinson In reply to MySQL LIMIT in SQL Server

SET RECORDS = 100
or something like that anyway.

Collapse -

Not quite

by just_chilin In reply to been a while but it's

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?

Collapse -

Mimic MySql Limit Function

by johnc In reply to Not quite

Not in SQLServer directly. It doesn't return a row counter so there is no way to say record x-z.

Collapse -

Well it has been a while

by Tony Hopkinson In reply to Not quite

Another piece of syntax I've forgot.
Have to get my RAM refresh cyle adjusted.

Collapse -

Use a temp table / table variable

by sanatorium In reply to Not quite

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

Collapse -

Oracle

by randall.d.gibson In reply to Not quite

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;

Collapse -

LIMIT alternative

by essenov In reply to Oracle

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/pagercontrols.asp

Collapse -

Here's a solution

by sholodak In reply to MySQL LIMIT in SQL Server

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

Collapse -

A generic solution

by arielglikmanwww In reply to MySQL LIMIT in SQL Server

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

Collapse -

DB2 inquiry

by r-deo In reply to MySQL LIMIT in SQL Server

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?

Back to Software Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums