0

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!

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Peter
  • 1
  • 3
  • Please update this thread, either accepting an answer or providing detail via comment or edit or even self answer why it doesnt answer your issue. – Daniel Brose May 18 '20 at 02:50

2 Answers2

1

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.

Adam C
  • 95
  • 1
  • 10
0

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

Community
  • 1
  • 1
Daniel Brose
  • 1,339
  • 10
  • 24