0

I have a table. In that table there is a column named CIS. This CIS column contains a unique number in each row. I want to get 50 rows from a specific row captured via CIS number.

For example lets say i have the following table

CIS  MODEL
---  -----
123    1
212    2
213    3
325    4
452    3
 .     .
 .     .
 .     .
841    4

And i just have a CIS number nothing else more. Let say my CIS number is 212. I want to get next 50 rows from the row with the CIS number 212. How can i do that?

MOD
  • 1,070
  • 3
  • 19
  • 41

2 Answers2

3

Does this do what you want?

select top 50 t.*
from table t
where cis > 212
order by cis;

It assumes that "next" means the next rows ordered by the CIS number.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • CIS column may not be ordered. It can be in a random sequence – MOD May 06 '15 at 15:19
  • 1
    Tables in relational databases represent *unordered* sets. There is no concept of the "next 50 rows" unless you have a column that specifies the ordering. – Gordon Linoff May 06 '15 at 15:24
  • 1
    By definition a table has no order so there is no concept of "next". If you have nothing to use as an ordering value you have no way to do what you are trying to do. – Sean Lange May 06 '15 at 15:25
2

Based on your question and comment, it sounds like you want to get the next 50 rows based on the order those rows were inserted in the table. As others have suggested, SQL Server does not have a method for retrieving by insert order. A SELECT query with no ORDER BY does not retrieve data in any particular order, even though it might seem to. If you are interested in why that is, you may want to review the post at https://stackoverflow.com/a/10064571/4656137

Since you want to know what is next and there is no concept of insert order, SQL needs to know what order to evaluate the rows to provide what you are looking for. One option might be if you have an auto-incrementing key column on that table, you could try to order by that (using Gordan's example).

Note: I would have simply commented on Gordon's response, but I don't have enough reputation to comment on others answers yet.

Hopefully this helps some.

Community
  • 1
  • 1
Fred
  • 91
  • 5