Home » SQL & PL/SQL » SQL & PL/SQL » create new record from LAST record with SQL (Oracle Database 11g)
create new record from LAST record with SQL [message #609384] Thu, 06 March 2014 03:51 Go to next message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
hi all gurus,

Just want to CREATE new record from LAST record fetched with SQL statement, is there any other simple and/or efficient way to achieve this? please help.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


SQL> ed
Wrote file afiedt.buf

  1  with data1 as (select deptno, dname, loc,
  2                        last_value (deptno)
  3                        over (order by deptno
  4                               rows between unbounded preceding
  5                                        and unbounded following) last_dept
  6                   from dept
  7                ),
  8       data2 as (select 50 deptno, dname, loc
  9                   from data1
 10                  where data1.deptno = last_dept
 11                )
 12  select deptno, dname, loc
 13  from   data2
 14  union all
 15  select deptno, dname, loc
 16  from  data1
 17* order by deptno
SQL> /

   DEPTNO DNAME          LOC
--------- -------------- -------------
       10 ACCOUNTING     NEW YORK
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO
       40 OPERATIONS     BOSTON
       50 OPERATIONS     BOSTON

5 rows selected.


regards.

[Updated on: Thu, 06 March 2014 04:04]

Report message to a moderator

Re: create new record from LAST record with SQL [message #609392 is a reply to message #609384] Thu, 06 March 2014 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 

SQL> select 50 deptno, dname, loc
  2  from (select dname, loc from dept order by deptno desc)
  3  where rownum = 1
  4  /
    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 OPERATIONS     BOSTON

Report message to a moderator

Re: create new record from LAST record with SQL [message #609393 is a reply to message #609392] Thu, 06 March 2014 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or if you also want the whole table:
SQL> select case when elem = 1 then deptno else 50 end deptno,
  2         dname, loc
  3  from (select deptno, dname, loc, 
  4               row_number() over (order by deptno desc) rn
  5        from dept),
  6       (select 1 elem from dual union all select 2 from dual)
  7  where elem = 1 or rn = 1
  8  order by deptno
  9  /
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 OPERATIONS     BOSTON

Report message to a moderator

Re: create new record from LAST record with SQL [message #609396 is a reply to message #609384] Thu, 06 March 2014 05:01 Go to previous messageGo to next message
oralover2006
Messages: 144
Registered: January 2010
Location: India
Senior Member
dear Michel Cadot,
thanks very much for your reply & interest to educate us Smile
yes, whole table required.

with your permission ...
SQL> ed
Wrote file afiedt.buf

  1  select deptno, dname, loc
  2    from dept
  3  union all
  4  select 50 deptno, dname, loc
  5    from (select dname, loc from dept order by deptno desc)
  6   where rownum = 1
  7* order by deptno
SQL> /

   DEPTNO DNAME          LOC
--------- -------------- -------------
       10 ACCOUNTING     NEW YORK
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO
       40 OPERATIONS     BOSTON
       50 OPERATIONS     BOSTON

5 rows selected.


regards.

[Updated on: Thu, 06 March 2014 05:12]

Report message to a moderator

Re: create new record from LAST record with SQL [message #609445 is a reply to message #609396] Thu, 06 March 2014 21:20 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The MODEL clause is another nice way to do this. I'm not so hot at the syntax of MODEL, but I'm sure if someone else doesn't step in you could have a go yourself and learn a new skill.
Ross Leishman

Report message to a moderator

Re: create new record from LAST record with SQL [message #609490 is a reply to message #609445] Fri, 07 March 2014 07:27 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member
Below is using Model Clause

select deptno,dname,loc
  from dept
 MODEL 
   dimension by (deptno) 
   measures (dname,loc)
   rules (
     DNAME[50] = 'OPERATIONS',
       loc[50] = 'BOSTON'
   )  
/
DEPTNO	DNAME	        LOC
--------------------------------------
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	        CHICAGO
40	OPERATIONS	BOSTON
50	OPERATIONS	BOSTON


Regards,
Anil MK

Report message to a moderator

Re: create new record from LAST record with SQL [message #609491 is a reply to message #609490] Fri, 07 March 2014 07:46 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Wrong, BOSTON and OPERATIONS are not hard coded, they are the values found in the "last" department.

Report message to a moderator

Previous Topic: Simple Question
Next Topic: Want to get first day of last month
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 15:25:05 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.