Postgres SELECT id LAG(id) OVER (ORDER BY id) FROM invoice WHERE id = 2-CodePudding

I've looked all over the internet and I fail to get this query running as expected. I've got a table of invoices and some invoices are related to one another because they belong to the same project.

My ticket says I've got to get the PREVIOUS invoice based on a provided invoice. Say Project A has 10 invoices, and I'm looking at invoice #4, I've got to write a query which will return the ID of the previous Invoice. Bear in mind, the invoice table is home to all sorts of projects, and each project could have many invoices on their own, so I want to avoid getting many IDs back and then iterating over them.

To illustrate the issue, I've written this fiddle. It works somewhat acceptably when I don't filter for steps.id, but that means returning hundreds of IDs to sift through. I've tried and tried but I can't seem to get the column previousStep to be kind of bound to the ID column.

CodePudding user response：

Simply find the invoice with the next largest id for the same project:

SELECT inv2.id
FROM invoice AS inv1
   JOIN invoice AS inv2
      ON inv1.project = inv2.project AND inv1.id > inv2.id
WHERE inv1.id = 1057638
ORDER BY inv2.id DESC
LIMIT 1;