Use SQL Server> Tips> Development Tips A Method for Recording Set Finding Specifications
Update Date: November 23, 2002
This tip is taken from the first phase of SQL Server magazine. For more techniques, please visit the SQL Server's Skills Center. Q: My table contains a single column primary key called IdValue. For a given IDVALUE value, I want to find the table row before and after the target value (assuming results are sorted by IdValue). How can I get the result of a collection-based approach without using a cursor? A: Transact-SQL is a collection-based language that uses it to locate a specific line in the result set is not a matter. However, the performance of the server-side ANSI Transact-SQL cursor is far less than a collection-based solution, so many techniques for learning to solve problems are very important, especially in the above problems. Take the ORDERS table in the Northwind database as an example. We can retrieve this problem this: How can we find the row before and after a row next to a specific line in the ORDERS table? Suppose we are sorted by the OrderID column. Creatively using SQL Server min (), max (), and TOP functions can help you solve the results of such results set. Listing 1 and 2 give two similar, only thin-difference solutions. Program List 1 provides a common solution because it uses @TargetORDER's Min () and max () endpoints. But in some cases, it is also useful to show the flexibility of TOP. Note that the query in the program list 2 will return empty set in the query in the program list 2, because the query is assumed to have a row in front of the @TargetORDER. Generally speaking, the method of generating result sets is more than one, and one of them is usually more efficient than others. When you evaluate Showplan and SET STATISTICS IO information about these two examples, you will find that the program list 2 of the TOP statement is slightly higher than the program list 1. The difference is very small because the sample data set is small, but in the absence of a variety of query methods, the performance of different methods is very important. -Brian Moran
Program List 1: Find the target line using the min () and max () function containing the or keyword
Declare @targetorder INT
Set @ TargetOrder = 10330
From Orders
Where orderid = @ TargetORDER
Or ORDERID = (SELECT MAX (ORDERID)
From Orders where OrderId <@targetorder)
Or ORDERID = (Select Min (ORDERID)
From Orders Where Orderid> @targetORDER)
Program List 2: Find target lines using TOP keywords
SELECT
TOP 3
*
From Orders
Where orderid> = (Select Max (OrderID) from Orders
Where orderid <@targetorder)
ORDER BY
ORDERID
Returns the use tip page.
