Keith Devens .com - Weblog: 'Limit' clause in MSSQL

Thursday, February 28, 2008

'Limit' clause in MSSQL

Solution 1: SQL Server Equivalent To MySQL And PostgreSQL Limit Clause - Joseph Scott’s Blog. Note: assumes unique keys.

SELECT TOP n *

FROM tablename

WHERE key NOT IN (

    SELECT TOP x key

    FROM tablename

    ORDER BY key

)

Solution 2: Finally a LIMIT clause for MS SQL. (Same solution recommended here, with a small explanation.)

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

Edit: The above is in SQL Server 2k... for 2005 there's a new row_number() function.

Permalink | 2 Comments | Tags: Microsoft, SQL | id 8382

11:24 (utc-6)
edited: 17:17 (utc-6)

Comments

Does that MS-SQL does not inherently support the LIMIT clause ?

No, MSSQL doesn't support the 'limit' clause that MySQL supports. That's the point of this post

∴ Keith | 14-Mar-2008 4:07pm est | http://keithdevens.com/ | #10569

Feel free to post a comment below. Please see my comment policy.

Formatting Rules (No HTML):

**bold**, *italic*, _underlined_, --strikeout--
"text"="url" creates a link, and URLs are auto-highlighted
Blockquote: Like e-mail, begin paragraph with > (greater-than sign)
Lists: begin paragraph with *,-, or + (unordered), or # (ordered)
Code block: ?!code:language=perl|php|sql|javascript|etc.{\n}...{\n}?!/code

RSS feed RSS feed for Keith's Weblog
Atom feed Atom feed for Keith's Weblog
Weblog archive
Recent comments
on 5 posts