CODEWITHSUNDEEP
sqloracle-database
I Stevenson
I Stevenson

Reputation: 844

How to get a single result with columns from multiple records in a single table?

Platform: Oracle 10g

I have a table (let's call it t1) like this:

ID  | FK_ID | SOME_VALUE | SOME_DATE
----+-------+------------+-----------
1   | 101   | 10         | 1-JAN-2013
2   | 101   | 20         | 1-JAN-2014
3   | 101   | 30         | 1-JAN-2015
4   | 102   | 150        | 1-JAN-2013
5   | 102   | 250        | 1-JAN-2014
6   | 102   | 350        | 1-JAN-2015

For each FK_ID I wish to show a single result showing the two most recent SOME_VALUEs. That is:

FK_ID | CURRENT | PREVIOUS
------+---------+---------
101   | 30      | 20
102   | 350     | 250

There is another table (lets call it t2) for the FK_ID, and it is here that there is a reference saying which is the 'CURRENT' record. So a table like:

ID  | FK_CURRENT | OTHER_FIELDS
----+------------+-------------
101 | 3          | ...
102 | 6          | ...

I was attempting this with a flawed sub query join along the lines of:

SELECT id, curr.some_value as current, prev.some_value as previous FROM t2
  JOIN t1 curr ON t2.fk_current = t1.id 
  JOIN t1 prev ON t1.id = (
    SELECT * FROM (
      SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) as rno FROM t1
          WHERE t1.fk_id = t2.id
      ) WHERE rno = 2
    )
  )

However the t1.fk_id = t2.id is flawed (i.e. wont run), as (I now know) you can't pass a parent field value into a sub query more than one level deep.

Then I started wondering if Common Table Expressions (CTE) are the tool for this, but then I've no experience using these (so would like to know I'm not going down the wrong track attempting to use them - if that is the tool).

So I guess the key complexity that is tripping me up is:

Otherwise, I can just write some code to first execute a query to get the 'current' value, and then execute a second query to get the 'previous' - but I'd love to know how to solve this with a single SQL query as it seems this would be a common enough thing to do (sure is with the DB I need to work with).

Thanks!

Upvotes: 0

Views: 96

Answers (2)

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

Try out this:

select t1.FK_ID ,t1.SOME_VALUE as CURRENT,
(select SOME_VALUE from t1 where p1.id2=t1.id and t1.fk_id=p1.fk_id) as PREVIOUS 
from  t1 inner join
(
  select t1.fk_id, max(t1.id) as id1,max(t1.id)-1 as id2 from  t1 group by t1.FK_ID 
) as p1 on t1.id=p1.id1

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

Try an approach with LAG function:

SELECT FK_ID , 
       SOME_VALUE as "CURRENT", 
       PREV_VALUE as Previous
FROM (
    SELECT t1.*,
           lag( some_value ) over (partition by fk_id order by some_date ) prev_value
    FROM t1
) x
JOIN t2 on t2.id = x.fk_id
     and t2.fk_current = x.id

Demo: http://sqlfiddle.com/#!4/d3e640/15

Upvotes: 2

Related Questions