CODEWITHSUNDEEP
sqldatabaseoracle-database
Adam Neighbors
Adam Neighbors

Reputation: 15

Oracle SQL: Selecting based on value in previous rows columns

I'm curious about writing a SQL query in Oracle based on the previous row in a list of returned rows. Basically I have to query our database for all rows that have a ID # of 2460, but the previous row (sorted by date) has an ID # of 2463. Is there any way to do this?

Sorry if this is confusing or a stupid question I'm very new and not really good at these sort of things. I'll be happy to clarify anything that needs clarifying.

Thanks!

Edit:

Here's the query I'm running based on Gordon's answer along with a screenshot of the results I'm getting vs the results I'm wanting.

select *
from (select activitytranledger.*, lag(reasontype) over (order by trandate) as prev_reason from activitytranledger) activitytranledger
where trandate between to_date('02/7/2017','MM/DD/YYYY')
and to_date('02/8/2017','MM/DD/YYYY')
and reasontype = 2460
and prev_reason = 2463
order by trandate
;

I'll then take the location # and query it for the specific day.

select *
from activitytranledger
where location = 5777
and trandate between to_date('02/7/2017','MM/DD/YYYY') 
and to_date('02/8/2017','MM/DD/YYYY')
order by trandate
;

Using the "Transid" I can find the specific row that was output by the first query. However, it does not seem to be giving me the desired results.

Wanted results (Notice how the row directly above the row with a "Reasontype" of 2460 has a reason type of 2463)

Current results (The row I highlighted should have the 2463 in the column I've pointed to)

Edit 2: switched 2463 and 2460

Upvotes: 1

Views: 3469

Answers (2)

Adam Neighbors
Adam Neighbors

Reputation: 15

So, it turns out I'm an idiot. @GordonLinoff's solution worked perfectly I just simply forgot to add the location # to the query below is the final query that I came up with. I'm able to replace the location # with my variable and can now insert it into my loop and it works like a charm. Thanks all that contributed!

select *
from (select activitytranledger.*, lag(reasontype) over (order by trandate) as
prev_reason from activitytranledger) activitytranledger
where trandate between to_date('02/7/2017','MM/DD/YYYY')
and to_date('02/8/2017','MM/DD/YYYY')
and location = 5777
and reasontype = 2460
and prev_reason = 2463
order by trandate
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

This is one interpretation of your question. Use lag() and a subquery:

select t.*
from (select t.*, lag(id) over (order by date) as prev_id
      from t
     ) t
where id = 2463 and prev_id = 2460;

Upvotes: 2

Related Questions