Next and Previous Records

[ponic]

Hi Tim

I have the following table structure and records. I would like to get the next and previous record i.e. if I am selecting

Code: Select all

product='LAPTOP' and PROD_YEAR='052012'

, then if I would like to get

Code: Select all

092006

as the previous record and vice versa if I am selecting

Code: Select all

product='LAPTOP' and PROD_YEAR='092006 '

, then I would like to get

Code: Select all

052012

as the next record.

How can I get this, I tried lag and lead, but I was getting inaccurate results.

Thanks

Code: Select all

CREATE TABLE PROD
(
  PROD_NO    VARCHAR2(12),
  PROD_YEAR  VARCHAR2(16)
);

Code: Select all

SET DEFINE OFF;
Insert into PROD
   (PROD_NO, PROD_YEAR)
 Values
   ('LAPTOP', '052012');
Insert into PROD
   (PROD_NO, PROD_YEAR)
 Values
   ('LAPTOP', '092006');
Insert into PROD
   (PROD_NO, PROD_YEAR)
 Values
   ('TABS', '092006');
Insert into PROD
   (PROD_NO, PROD_YEAR)
 Values
   ('TABS', '052012');
Insert into PROD
   (PROD_NO, PROD_YEAR)
 Values
   ('PHONES', '052012');
Insert into PROD
   (PROD_NO, PROD_YEAR)
 Values
   ('PHONES', '092006');
COMMIT;

[Tim...]

Hi.

Sounds like you need the LAG and LEAD analytic functions.

http://www.oracle-base.com/articles/mis ... ctions.php

Cheers

Tim...

[ponic]

Tim,
For the following query I should be getting

Code: Select all

052012

however I a getting result

Code: Select all

0

.
What's wrong with my query?

Code: Select all

SELECT PROD_NO,
       LEAD(PROD_YEAR, 1, 0) OVER (ORDER BY PROD_YEAR) AS next_val
FROM   prod where PROD_NO='LAPTOP' and PROD_YEAR='092006'

[Tim...]

Hi.

Be careful when ordering by a number stored in a string. You may not get the result you expect. Order by TO_NUMBER(PROD_YEAR).

Are you sure you expect that number? You are using LEAD, which looks for the value in the next row. I would expect to get the value you suggest if you were using LAG.

Cheers

Tim...

[ponic]

Ok, I managed to find the problem, 052012 is less than 092006 because it is taken as string. I need to convert this to date.
052012 is nothing but May 2012 and 092006 is September 2006.

[Tim...]

Hi.

LOL. I didn't even look at the column date, just the fact it was a string.

Yes. Order by TO_DATE(PROD_YEAR,'MMYYYY') to get the correct order.

Cheers

Tim...

[ponic]

I tried this but no results.

Code: Select all

SELECT PROD_NO,
       LEAD(to_date(PROD_YEAR,'mmyyyy'), 1, '') OVER (ORDER BY to_date(PROD_YEAR,'mmyyyy')) AS next_val
FROM   prod where PROD_NO='LAPTOP' and to_date(PROD_YEAR,'mmyyyy')=to_date('092006','mmyyyy')

[ponic]

I managed to resolve this way.

Code: Select all

select *
from (
    select PROD_NO, getdate(PROD_YEAR)PROD_YEAR,         
           to_char(lag(getdate(PROD_YEAR),1,'') over (partition by PROD_NO order by getdate(PROD_YEAR)),'MMYYYY') as prev_val,
           to_char(lead(getdate(PROD_YEAR),1,'') over (partition by PROD_NO order by getdate(PROD_YEAR)),'MMYYYY') as next_val
    from prod
)

and I created a function called getdate

Code: Select all

CREATE OR REPLACE FUNCTION getdate (param      VARCHAR2
                                                         )    RETURN DATE
IS
    my_date    DATE;
BEGIN
   
        SELECT    TO_CHAR (
                        TO_DATE (
                                '01/'
                            || SUBSTR (param, 0, 2)
                            || '/'
                            || SUBSTR (param, 3),
                            'dd/mm/yyyy'
                        ),
                        'DD-MON-YYYY'
                    )
          INTO    my_date
          FROM    DUAL;
 

    RETURN my_date;

END getdate;
/

[Tim...]

Yak. You shouldn't need all that. That's going to be really slow with a large amount of data. You should only need to order by the TO_DATE, like I mentioned.

I'm replying on an iPad, so I can't run it to check.

Cheers

Tim...

[ponic]

I tried to_date() but didn,t work.

Regards

[Tim...]

Hi.

Works fine for me:

Code: Select all

SELECT *
FROM   (SELECT PROD_NO,
            prod_year,
            LAG(PROD_YEAR, 1, 0) OVER (ORDER BY TO_DATE(PROD_YEAR,'DDYYYY')) AS prev_val,
            LEAD(PROD_YEAR, 1, 0) OVER (ORDER BY TO_DATE(PROD_YEAR,'DDYYYY')) AS next_val
      FROM   prod where PROD_NO='LAPTOP')
WHERE prod_year='092006';

PROD_NO      PROD_YEAR        PREV_VAL         NEXT_VAL
------------ ---------------- ---------------- ----------------
LAPTOP       092006           0                052012

SQL>

Analytic functions work on result set from the query. If you limit rows from the query too early, like including "WHERE prod_year='092006'" in the main query, you remove the rows from the result set, so the analytic function can't work with them. So you have to build your basic result set (the inner query), then filter data from it (the outer query).

Much nicer than using a function with redundant type conversions in it.

Cheers

Tim...

[ponic]

Thanks Tim for the improved query and this makes sense.

Appreciated.

[Tim...]