This forum is currently locked. You can't register or post questions at this time. (read more)

Next and Previous Records

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Next and Previous Records

Postby ponic » Wed Jun 13, 2012 1:19 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Next and Previous Records

Postby Tim... » Wed Jun 13, 2012 3:27 pm

Hi.

Sounds like you need the LAG and LEAD analytic functions.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Next and Previous Records

Postby ponic » Wed Jun 13, 2012 4:09 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Next and Previous Records

Postby Tim... » Wed Jun 13, 2012 4:38 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Next and Previous Records

Postby ponic » Wed Jun 13, 2012 4:39 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Next and Previous Records

Postby Tim... » Wed Jun 13, 2012 4:47 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Next and Previous Records

Postby ponic » Wed Jun 13, 2012 5:10 pm

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
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Next and Previous Records

Postby ponic » Wed Jun 13, 2012 5:35 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Next and Previous Records

Postby Tim... » Wed Jun 13, 2012 6:51 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Next and Previous Records

Postby ponic » Thu Jun 14, 2012 1:48 am

I tried to_date() but didn,t work.

Regards

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Next and Previous Records

Postby Tim... » Thu Jun 14, 2012 7:55 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Next and Previous Records

Postby ponic » Thu Jun 14, 2012 3:32 pm

Thanks Tim for the improved query and this makes sense.

Appreciated.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Next and Previous Records

Postby Tim... » Thu Jun 14, 2012 3:49 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron