On Oct 6, 9:16 pm, krish newlife <krishnewl...@gmail.com> wrote: > hey it can be done using analytical functions sql > > > > On Thu, Oct 6, 2011 at 3:34 PM, ddf <orat...@msn.com> wrote: > > > On Oct 6, 4:52 am, SANDEEP REDDY <tosandeepyan...@gmail.com> wrote: > > > Hi Friends Here Is The Solution For Ur Question > > > > select a.name NAME1, b.name NAME2 > > > FROM SAMPLE a, SAMPLE b > > > where a.name<b.name > > > > Enjoyyyyy > > > & > > > All The Best > > > > On Sep 1, 7:59 pm, ddf <orat...@msn.com> wrote: > > > > > On Sep 1, 2:46 am, Siva <cs.prasad...@gmail.com> wrote: > > > > > > Hi All, > > > > > > In Table1 I have data like this. > > > > > > Column1 > > > > > ----------------- > > > > > > Record1 > > > > > Record2 > > > > > Record3 > > > > > Record4 > > > > > > And in Table 2 the data has to be inserted in below manner. > > > > > > Column1 Column2 > > > > > ------------------------------- > > > > > Record1 Record2 > > > > > Record1 Record3 > > > > > Record1 Record4 > > > > > Record2 Record3 > > > > > Record2 Record4 > > > > > Record3 Record4 > > > > > > Please let me know how to achieve this. > > > > > > Regards. > > > > > You logically think through the problem and arrive at a solution. > > > > Since this is probably homework you should post what you've done to > > > > achieve these results. Once we see your work we can correct areas, as > > > > necessary, to lead you to the answer. > > > > > David Fitzjarrell- Hide quoted text - > > > > - Show quoted text - > > > I think not since the question asks how to INSERT data into a table in > > that order. As far as we know there is no d > > > -- > > You received this message because you are subscribed to the Google > > Groups "Oracle PL/SQL" group. > > To post to this group, send email to Oracle-PLSQL@googlegroups.com > > To unsubscribe from this group, send email to > > oracle-plsql-unsubscr...@googlegroups.com > > For more options, visit this group at > >http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text - > > - Show quoted text -
No example to illustrate your point??? Makes this a rather lame post, in my opinion. Trying your suggestion in a number of ways: SQL> select column1, lead(column1) over (order by column1) column2 2 from srcins; COLUMN1 COLUMN2 -------------------- -------------------- Record1 Record1 Record1 Record2 Record2 Record2 Record2 Record3 Record3 Record3 Record3 Record4 Record4 Record4 Record4 8 rows selected. SQL> That's not the desired result; we try again: SQL> select column1, lag(column1) over (order by column1) column2 2 from srcins; COLUMN1 COLUMN2 -------------------- -------------------- Record1 Record2 Record1 Record3 Record2 Record4 Record3 SQL> That isn't, either. Another try: SQL> with a as ( 2 select column1, row_number() over (order by column1) 3 from srcins 4 ), 5 b as ( 6 select s.column1 7 from srcins s, a 8 where s.column1 > a.column1 9 ) 10 select distinct a.column1, b.column1 11 from a, b 12 where a.column1 < b.column1 13 order by 1,2; COLUMN1 COLUMN1 -------------------- -------------------- Record1 Record2 Record1 Record3 Record1 Record4 Record2 Record3 Record2 Record4 Record3 Record4 6 rows selected. SQL> That is the correct result set but the analytic function didn't do anything useful. The most efficient statement is: SQL> insert into ordins 2 select a.column1, b.column1 3 from srcins a, srcins b 4 where a.column1 < b.column1; 6 rows created. SQL> SQL> select * From ordins; COLUMN1 COLUMN2 -------------------- -------------------- Record1 Record2 Record1 Record3 Record1 Record4 Record2 Record3 Record2 Record4 Record3 Record4 6 rows selected. SQL> which wasn't posted as an insert statment, simply as a select. Unless someone can come up with an analytic function query that can produce that result set I think the suggestion was made in haste without first checking its validity. My two cents. David Fitzjarrell -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
