0

I believe this is possible in mysql, but what about sql server 2008? For example:

   **ID             Type**
   11111          dgf-324-fhj
   11111          sdg-654-fhd
   22222          djg-234-dfh
   23333          uoi-738-sdf

If type is the PK, and lets say my query returns the first 11111 row, how can I create an SQL Statement that will parse me to the next or previous row? Any help or a point to the right direction would be awesome. The program is asp.net/c#/visual studio 2010, Thanks!

javasocute
  • 648
  • 2
  • 11
  • 28

2 Answers2

2

Remember that tables represent sets of unordered rows. Thus, the concept of "next" and "previous" has no meaning unless you dictate an order. In your example, a given Select statement is not guaranteed to return say dgf-324-fhj before sdg-654-fhd unless you specify an order. However, let's suppose that the order by Type we can use a ranking function like so:

With OrderedItems As
    (
    Select Id, Type, Row_Number() Over ( Order By Type ) As Num
    From SourceData
    )
Select Id, Type
From OrderedItems
Where Num = 2

SQL Fiddle example

Let me also mention there are problems with this approach. If someone inserts a new row that becomes the "new" row 2 after you have queried for row 1, you will get a result different than you expect.

Thomas
  • 63,911
  • 12
  • 95
  • 141
0

Look at this answer regarding pagination: What is the best way to paginate results in SQL Server

If you wanted to do a single row at a time then you'd just change the RowNum filters to be 1 at a time.

Community
  • 1
  • 1
Levi W
  • 805
  • 6
  • 13