1

I am curious if it's possible to build a SQL query to do what I want. I know I can do it programatically by parsing a table and building an array of recnos, but am curious if any of you experts out there know if there's a simpler/more efficient way to do this?

I have a table that will be queried in a specific order such as:

SELECT * from my_table ORDER by id1,id2,date

The table also has an indexed column called "recno" that allows me to pull a specific row like:

SELECT * from my_table where recno=1234567

I assume the recno values are not in any specific order.

What I'd like to be able to do is pull a specific row, but instead of getting the row with recno=1234567, I'd like to be able to pull the row before it, the row after it, or x rows before/after, according to the "ORDER BY attributes" in the first example.

In other words, give a recno (specific row) as a reference, how can I pull a row before/after according to a certain sort order?

What do you think is the best approach to doing this? I'm working an extremely large table so the more efficient this query is, the better things will be.

Thanks for any advice!

I'm using MariaDB

Trent Three
  • 211
  • 2
  • 10
  • _I assume the recno values are not in any specific order._ Nothing in the database is in any specific order, if you want order you have to place and ORDER BY on a query – RiggsFolly Sep 20 '22 at 16:06
  • What exactly do you mean by the row before and the row after EXACLY. Do you mean the row with recno-1 and recno+1 OR Something else – RiggsFolly Sep 20 '22 at 16:08
  • I think an example of inputs and outputs woudl help here, maybe even a dbfiddle with some sample data – RiggsFolly Sep 20 '22 at 16:09
  • One way I know how this can be solved is not via SQL queries... let me explain: I read the database using the specific ORDER BY until I find the recno, along the way, I note the record numbers of each row until I find the matched recno, then I return the recno of the row before/after... but that requires parsing the entire database. Wondering if there's an easier way. – Trent Three Sep 20 '22 at 16:10
  • @RiggsFolly He says "according to the ORDER BY attributes. I think this should be doable using window functions and `ROWNUM()`, but I don't have much experience with them. – Barmar Sep 20 '22 at 16:10
  • I'm not sure you even need `recno`, just use `LAG()` and `LEAD()`. – Barmar Sep 20 '22 at 16:11
  • I'm using Mariadb 5.5.68 - so maybe LAG function not available? – Trent Three Sep 20 '22 at 16:15
  • you can emulate it https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql – nbk Sep 20 '22 at 16:17
  • Although I think Barmar may be onto something.. the LAG function says: The LAG function accesses data from a previous row according to the ORDER BY clause without the need for a self-join. This sounds like what I want, but not available in my DB, however, the reference suggests the same could be accomplished by some sort of SELF JOIN? I'd like to find out more about that. – Trent Three Sep 20 '22 at 16:18
  • please read my previous comment – nbk Sep 20 '22 at 16:19
  • Interesting, so perhaps something like this? SET @lastrec=-1; SELECT *,@lastrec from my_table WHERE recno=1234567 ORDER BY id1,id2,date? Would that work? – Trent Three Sep 20 '22 at 16:23
  • give it a try, there a sql fiddles where you can test code, and show others if there is a problem see https://dbfiddle.uk/ – nbk Sep 20 '22 at 16:24
  • I tried it and while it doesn't generate an error, it doesn't really work in the examples given on nbk's stackoverflow reference. It looks like the "@field -1" reference may actually be a math operation and not a database operation. My fallback position is to create some sort of stored procedure to look forward/backwards, but I still have a feeling there may be a better way to do this. – Trent Three Sep 20 '22 at 18:54
  • I think I found something that kind of works... check this out: SET @prec=-1; SET @nrec=+1; SELECT @prec previous_recno,@nrec next_recno,@prec:=recno recno1,@nrec:=recno, etc from my_table ORDER BY x,y,z The problem is this only works when you parse the table and if can give you next/previous record numbers but the next one doesn't work for the first row for some reason. – Trent Three Sep 20 '22 at 19:57
  • Another possible solution/question: create a separate table of (rownum,recno) using the desired sort order, then JOIN that table to the main query on recno (with no sorting obviously), then pull the rownum from the secondary table, pull the record with rownum-1 or rownum+1, to get the recno of the previous/next records. What's the quickest way to create this alternate table? – Trent Three Sep 20 '22 at 20:00
  • Maybe using `LIMIT 1 offset 8`. ... it Returns 9th record based on some order https://www.mariadbtutorial.com/mariadb-basics/mariadb-limit/ – xQbert Sep 20 '22 at 21:09

1 Answers1

0

It seems the easiest approach is to create a separate table of (rownum,recno) using the desired sort order, then JOIN that table to the main query on recno (with no sorting obviously), then pull the rownum from the secondary table, pull the record with rownum-1 or rownum+1, to get the recno of the previous/next records.

Trent Three
  • 211
  • 2
  • 10