0

I am currently working with a Firebird database and I am trying to identify/flag records
that have a zero pos_buildcount value and where the previous record to the zero record has a pos_buildcount value that is not 255. I can do it in excel but I want to do it in a SQL query as excel can only deal with a certain amount of records. Essentially I want my results to look like the following image:

Required Results

I have tried the following links to try select individual records but most solutions use a id number which my database doesn't have (I don't know why) or the solutions use the row_number() command which Firebird does not have.

Selecting the last record that meets a condition

Is there a way to access the “previous row” value in a SELECT statement?

user5438246
  • 31
  • 7
  • Please provide DDL and sample data in the form of insert statement. And show what you tried. Also, which Firebird version are you using? Firebird **does have** [`row_number()`](https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-windowfuncs.html) (since Firebird 3). Also, that `Vehicle` column looks like something that qualifies as an ID to me. – Mark Rotteveel Oct 11 '18 at 10:01
  • My apologies that image shows a sequential number but in the actual database there are duplicate vehicles and therefore it cannot be used as a ID. I will chat to one of our developers to see if I can get the information you require – user5438246 Oct 11 '18 at 10:18
  • In any case: with Firebird 3 this will be easier to do than with earlier versions. It would also be good to know what determines the order of what determines what 'previous' is. – Mark Rotteveel Oct 11 '18 at 12:44
  • Maybe you have to use MS Access if MS Excel is not good enough? They even used to use the same DB engine, MS Jet in the past. – Arioch 'The Oct 12 '18 at 15:58
  • 1
    Also edit the question and add Firebird version into your question. You can query it by `SELECT rdb$get_context( 'SYSTEM' , 'ENGINE_VERSION' ) from rdb$database` command – Arioch 'The Oct 12 '18 at 16:13

1 Answers1

0

use a id number which my database doesn't have

Then add it.

SQL is a language of sets: https://en.wikipedia.org/wiki/Set_(mathematics)

By definition, elements in the set are not located left or right to one another, to the North or to the South, or before and after. They just exists. In unordered way.

So, if you want your rows ordered - then you have to ADD some ordering value (field, column) into it, and then populate that value. And then reading from the table, if you want it, you may ask to order the results by that value.

As of now, Firebird has all the rights to read rows in any order it might like, even to change the order of the rows on disc (it will not, but that is implementation ndetail and it may change in future).

You have to add some ID column and then to populate it. This would make "last record" or "prior record" or "row above" meaningful idioms: "object with ID one less than ID of current object". As of now in SQL terms there is no any meaning in "last record" and reliable formulating a query is not possible.

After that changing the flag column in the table becomes a trivial MERGE statement.

MERGE INTO MyTable T1
USING MyTable T2 
   ON (T2.ID = T1.ID - 1) AND (T2.pos_buildcount <> 255)
WHEN MATCHED THEN
  UPDATE SET T1.flag = 1

http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-merge.html

Or, without adding the column into the table, but making a "virtual" column in the query, joining the table upon itself.

https://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join

SELECT T1.*, T2.Vehicle as Flag
FROM MyTable T1 
LEFT JOIN MyTable T2
       ON (T2.ID = T1.ID - 1) AND (T2.pos_buildcount <> 255)

Granted it would only work if ID column would be filled with integers with no gaps. Otherwise there would be other definitions what "prior record" means than T2.ID = T1.ID - 1, but the idea holds. Define what "prior" means in terms of real data on real columns and then you can compare the table with itself.

Arioch 'The
  • 15,799
  • 35
  • 62