Mimic The MySQL LIMIT Feature in Microsoft SQL Server
My SQL includes a nifty feature that lets you select only rows 1-10, 11-20, or any set you want. Microsoft SQL Server does not include this feature. This recipe will show you how to mimic the same feature easily without stored procedures.
Microsoft's T- SQL includes the TOP syntax, which allows you to select only the top x number of records from your SQL query. This is very useful for testing purposes or selecting a single record, but less useful for production applications where you might want to do some sort of paging.
What we will do is use the TOP feature in reverse. We will select the top 20 records in a particular order, and then reverse the order and select the top 10 of those.
SELECT TOP 10 *
FROM (SELECT TOP 20 * FROM Orders ORDER BY OrderDate) as T
ORDER BY OrderDate DESC
The subselect SQL statement will return the top 20 orders by date. Then we will select the top 10 of those and reverse the sort order back.
If we want to select orders 20-30 in the list, we would simply do this:
SELECT TOP 10 *
FROM (SELECT TOP 30 * FROM Orders ORDER BY OrderDate) as T
ORDER BY OrderDate DESC
It's probably not useful for a true production application, but in a pinch this works pretty well.
Also see ...
H3How to find the system language of a server running SQL2000 using a SQL statement./H3PI recently had a client who was having problem with date format in SQL2000 after moving from a UK based server to a German one. In order to check that the database was set to the correct language locale, ru
H3Run the following code in SQL Server 2000 Query Analyzer to return the Version and Service pack useful if you're having problems on a server and don't know if you're running the latest patches or not./H3Pdiv class="code" br /SELECT SERVERPROPERTY&40;'productversion'&41;, SERVERPROPE