LIMIT in MS SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

LIMIT in MS SQL

Author

Topic

adrian_quah
Starting Member

5 Posts

Posted - 2003-01-30 : 00:04:32

Hello guys,

actually, i'd like to convert the following query which is Okay in MySQL to MS SQL Server query format..

SELECT * FROM `customer_details` order by 'Customer_ID' LIMIT 0, 10

Any help? thanx

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-30 : 00:10:37

SELECT top 10 * FROM customer_details order by Customer_ID

(not really sure what limit does).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adrian_quah
Starting Member

5 Posts

Posted - 2003-01-30 : 01:07:54

SELECT top 10 * FROM customer_details order by Customer_ID

is good...but it only returns the first 10 records...how can i return say like, from records 11 - 20 by index?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-30 : 01:12:19

select top 10 * from (select top 20 * from tbl order by fld desc) as a order by fld

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

adrian_quah
Starting Member

5 Posts

Posted - 2003-01-30 : 01:18:06

That's lightning speed...Thanks, i'll try it out now ;)

adrian_quah
Starting Member

5 Posts

Posted - 2003-01-30 : 01:30:54

nr, tried out ur suggestion. what i'd like is to get the records like this..I'll illustrate with a design of my db..

Customer_ID, Customer_Name, Address1
0010,John,USA
0011,Jane,Toronto
0012,Adrian,Malaysia
0013,Stephen,China
0014,Kataga,Japan

what if just want to return not the top 3 rows, but the middle three?

adrian_quah
Starting Member

5 Posts

Posted - 2003-01-30 : 02:30:43

SELECT * FROM `customer_details` order by 'Customer_ID' LIMIT 20, 10

nr, fyi, This'll return 10 records starting from the Index 'Customer_ID'
numbered 20...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-01-30 : 07:26:10

have a look at the article called "What's after Top"

this is a prime example of same..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources