Home » SQL & PL/SQL » SQL & PL/SQL » Sql toughest query
Sql toughest query [message #202620] Fri, 10 November 2006 10:07 Go to next message
srinivas.k2005
Messages: 340
Registered: August 2006
Senior Member
Hi,

I have a table 'A' with structure as follow:

Database name value
ORA SCOTT 3
ORA SCOTT 4
ORA SCOTT 5
ORCL TEST 4
ORCL TEST 5
ORCL TEST 6

Another table 'B' with structure as follow:

Database1 name1 value1 database2 name2 value2.


Now with a query i want the data of table 'A' to be inserted into table 'B': as below

Database1 name1 value1 database2 name2 value2.
ORA SCOTT 3 ORCL TEST 4
ORA SCOTT 4 ORCL TEST 5
ORA SCOTT 5 ORCL TEST 6

Thanks,
Srinivas







Re: Sql toughest query [message #202646 is a reply to message #202620] Fri, 10 November 2006 12:07 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What condition should records from table A satisfy to make pairs like you showed in desired result?

For example, what makes (ORA Scott 3) + (ORCL Test 4) a pair?
Re: Sql toughest query [message #202660 is a reply to message #202646] Fri, 10 November 2006 15:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that you want row i from the set {Database=ORA,Name=SCOTT} paired up with row i from the set {Database=ORCL,Name=TEST}, both sets ordered by value, you could use something like this

(untested)
SELECT t1.database  database1
      ,t1.name      name1
      ,t1.value     value1
      ,t2.database  database2
      ,t2.name      name2
      ,t2.value
FROM   (SELECT database
              ,name
              ,value
              ,row_number() over (partition by database,name) rnum
        FROM   table_a
        WHERE  database = 'ORA') t1
      ,(SELECT database
              ,name
              ,value
              ,row_number() over (partition by database,name) rnum
        FROM   table_a
        WHERE  database = 'ORCL') t2
WHERE   t1.rnum = t2.rnum;


But, having written that, I don't think it's going to be what you want....
Re: Sql toughest query [message #202689 is a reply to message #202660] Sat, 11 November 2006 00:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
you will need an order by value if you want to "both sets ordered by value"
Re: Sql toughest query [message #202747 is a reply to message #202660] Sat, 11 November 2006 11:49 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi JRowBottom,

I tried the query and it gives the errors as follows:

ERROR at line 10:
ORA-30485: missing ORDER BY expression in the window specification

Bye
Ashu
Re: Sql toughest query [message #202860 is a reply to message #202660] Mon, 13 November 2006 01:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hey - it does say Untested!

Just add 'ORDER BY value' after the two Partition By clauses
Re: Sql toughest query [message #203292 is a reply to message #202620] Tue, 14 November 2006 12:03 Go to previous message
srinivas.k2005
Messages: 340
Registered: August 2006
Senior Member


thanks a lot for all your replies.
Previous Topic: Populating LOB column
Next Topic: put data from excel to database
Goto Forum:
  


Current Time: Wed Dec 07 22:37:30 CST 2016

Total time taken to generate the page: 0.08646 seconds