Here Is The Way To Write Such Queries In RealTime Operations
Mentioning The Column Names Helps A lot In Realtime Bcoz Oracle
Arranges The Columns In Different Way While Creating The Tables...
INSERT INTO SAMPLE1(column1,column2)
( select a.column1,b.column1 from sample a,sample b
where a.column1<>b.column1 and a.column1<b.column1)
On Oct 12, 1:56 am, ddf <[email protected]> wrote:
> On Oct 6, 9:16 pm, krish newlife <[email protected]> wrote:
>
>
>
>
>
>
>
>
>
> > hey it can be done using analytical functions sql
>
> > On Thu, Oct 6, 2011 at 3:34 PM, ddf <[email protected]> wrote:
>
> > > On Oct 6, 4:52 am, SANDEEP REDDY <[email protected]> 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 <[email protected]> wrote:
>
> > > > > On Sep 1, 2:46 am, Siva <[email protected]> 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 [email protected]
> > > To unsubscribe from this group, send email to
> > > [email protected]
> > > 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
