CODEWITHSUNDEEP
sqlsql-server
Peter
Peter

Reputation: 1

Using returned variables in a SQL Server query

I am using SQL Server 2012 and I have a query which returns two columns, the Item number and the Row number of that item in the query:

Row    Item  
--------------
 1     1234  
 2     5632  
 3     4213  
 4     0912

Before I run the query I will know that I am only interested in the row containing Item 5632 and X number of rows following the one containing item 5632 (in this case lets just say rows 2 and 3). However, I will not know ahead of time what row Item 5632 is in.

I would like to somehow say

Where Row >= Row of Item 5632  
  And Row <= Row of Item 5632 + X

Is this possible to do? Thank you all!

Upvotes: 0

Views: 87

Answers (2)

Adam C
Adam C

Reputation: 103

Supposing the query you have now is SELECT RowNo, Item FROM Tbl, the following query can replace it and do what you want:

DECLARE @Item = 5632
DECLARE @ItemRowNo = SELECT RowNo FROM Tbl WHERE Item = @Item
DECLARE @Qty = 2

SELECT RowNo, Item 
FROM Tbl
WHERE Item >= @ItemRowNo
AND Item < (@ItemRowNo + @Qty)
ORDER BY RowNo

If you give me your actual current query, I can update this answer to reflect it.

You may choose to declare less things than what I did if they will be constant, but I'm guessing that you will in fact be SELECTing them from elsewhere.

Upvotes: 1

Daniel Brose
Daniel Brose

Reputation: 1403

More than one way to do this, im more accustomed to nested queries

Nested select statement in SQL Server

SQL nested query in SQL server

More specifically:

SELECT TOP 3 a.row, a.item FROM tableA a 
WHERE a.row >= (SELECT row FROM tableA WHERE item = 5632) 
ORDER BY a.row

TOP doesnt worry about actual value of ROW, just limits number of retrieved records

http://www.w3schools.com/sql/sql_top.asp

http://www.w3schools.com/sql/sql_where.asp

http://www.w3schools.com/sql/sql_orderby.asp

Upvotes: 0

Related Questions