1

So to try and keep things brief, I need to get all the information in my database between rows x and y. Now I have a globalId column which auto increments, however, when I delete a row from the database (which I will be doing), the next piece of data inserted will have a globalId +1 of the data deleted, as expected. This then creates an issue because I can't iterate simply with a for loop between my x and y value, and get all data points where globalId = i. If I could get the index of the row, that, however, would not have gaps from past rows which have now been deleted. I've tried many methods to get the row index but nothing seems to be working.

I am using Java for this and so I have a ResultSet returned from my query if that helps.

Jev4n5
  • 13
  • 3

1 Answers1

1

Here is a platform-independent solution:

String sqlQuery = "SELECT .... FROM ... ORDER BY global_id ASC";
ResultSet rs = runQuery(sqlQuery); // a made-up method to hide the details
final int x = 10;
final int y = 20;
int i = 0;
while (i < x && rs.next()) {
    i++;
}
while (i < y && rs.next()) {
    // retrieve record
    i++;
}

So what do we do here?

  • The order by clause makes sure that we can scroll to the nth item - regardless of the holes in the id sequence
    • the first while loop goes to the beginning of your window
    • the second while loop goes through the exact records you need. You can process them.

Alternatively, you can use the limit-offset, as explained here:

SELECT .... FROM ... ORDER BY global_id ASC LIMIT 20 OFFSET 10

Just a warning regarding offset: although it looks elegant, it has its own quirks. Some experts suggest not to use it.

Tamas Rev
  • 7,008
  • 5
  • 32
  • 49