Home » Other » Training & Certification » show 2 columns selecting on 1 table.
show 2 columns selecting on 1 table. [message #269052] Thu, 20 September 2007 10:30 Go to next message
ada26
Messages: 15
Registered: September 2007
Location: cleveland
Junior Member
i have here a problem that i need to show 2 same colums selecting from 1 table.

this is the table below.

select emp_id, first_name, last_name
from employee;

SQL> select employee_id, first_name, last_name
2 from employees
3 where first_name = 'BOBBY'
4 and last_name = 'JORDAN';
5731 BOBBY JORDAN
46878 BOBBY JORDAN
47436 BOBBY JORDAN
49962 BOBBY JORDAN
50412 BOBBY JORDAN
102483 BOBBY JORDAN
102483 BOBBY JORDAN
102483 BOBBY JORDAN
102483 BOBBY JORDAN
102483 BOBBY JORDAN

10 rows selected.

however i want my query to give me an output something like this:
OLD NEW
5731 102483 BOBBY JORDAN
46878 102483 BOBBY JORDAN
47436 102483 BOBBY JORDAN
49962 102483 BOBBY JORDAN
50412 102483 BOBBY JORDAN
102483 102483 BOBBY JORDAN

Re: show 2 columns selecting on 1 table. [message #269058 is a reply to message #269052] Thu, 20 September 2007 10:44 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Come on...if you want us to do your homework for you, at least put a little more thought and effort into writing a good question...do a quick read of the orafaq forum guide in the yellow box above the forum messages.
Re: show 2 columns selecting on 1 table. [message #269059 is a reply to message #269058] Thu, 20 September 2007 10:48 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

I think this question should go to the homework section.
Please format your queries and results before posting for get a quick answer.
Any moderator please transfer this to homework section.

Cheers
Soumen
Re: show 2 columns selecting on 1 table. [message #269062 is a reply to message #269052] Thu, 20 September 2007 10:53 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Well how about that...I didn't even know we had a homework section...shows you how out of touch I am.
Re: show 2 columns selecting on 1 table. [message #269063 is a reply to message #269062] Thu, 20 September 2007 10:56 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Here is the solution:
with data as
(select 5731 empno,'BOBBY' first_name,'JORDAN' last_name from dual
    union all 
select 46878  empno,'BOBBY' first_name,'JORDAN' last_name  from dual
    union all
select 47436  empno,'BOBBY' first_name,'JORDAN' last_name  from dual
    union all
select 49962  empno,'BOBBY' first_name,'JORDAN' last_name  from dual
    union all
select 50412  empno,'BOBBY' first_name,'JORDAN' last_name from dual
    union all
select 102483 empno,'BOBBY' first_name,'JORDAN' last_name from dual
)
select empno old ,(select max(empno) from data) new, first_name,last_name
from data

Cheers
Soumen
Re: show 2 columns selecting on 1 table. [message #269068 is a reply to message #269063] Thu, 20 September 2007 11:25 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
That is just A query that happens to get the same output as the poster asked.
The poster however is so totally unclear in his question, there is no way you can be sure this is what he wants.
There are multiple rows with empno 102483. Which should be joined to which other row? Does there need to be row with 102483 for each row with another number?
What makes 102483 the number that needs to come in the second column? The fact that it is a duplicate empno? The fact that it is the max value? The fact that it has 6 digits?
Re: show 2 columns selecting on 1 table. [message #269075 is a reply to message #269068] Thu, 20 September 2007 12:09 Go to previous messageGo to next message
ada26
Messages: 15
Registered: September 2007
Location: cleveland
Junior Member
i have employee_id, first_name and last_name colums etc... and on that table i have thousand of records.

i want to merge the records of the person if that person has repetitive employee id.
Re: show 2 columns selecting on 1 table. [message #269076 is a reply to message #269052] Thu, 20 September 2007 12:11 Go to previous messageGo to next message
ada26
Messages: 15
Registered: September 2007
Location: cleveland
Junior Member
i mean duplicate id's with the most number must be on the column 2 because that is his new employee id.
Re: show 2 columns selecting on 1 table. [message #269093 is a reply to message #269076] Thu, 20 September 2007 12:37 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Very bad design.
Relying on employee name instead of id. Embarassed

Anyway, Does the following meet your requirement
with data as
(select 5731 empno,'BOBBY' first_name,'JORDAN' last_name from dual
  union all 
 select 46878  empno,'BOBBY' first_name,'JORDAN' last_name  from dual
  union all
 select 47436  empno,'BOBBY' first_name,'JORDAN' last_name  from dual
  union all
 select 49962  empno,'BOBBY' first_name,'JORDAN' last_name  from dual
  union all
 select 50412  empno,'BOBBY' first_name,'JORDAN' last_name from dual
  union all
 select 102483 empno,'BOBBY' first_name,'JORDAN' last_name from dual
  union all
 select 102483 empno,'BOBBY' first_name,'JORDAN' last_name from dual
  union all
 select 102483 empno,'BOBBY' first_name,'JORDAN' last_name from dual
)
select a.empno old ,b.empno new, a.first_name,a.last_name
  from (select distinct empno,first_name,last_name from data)a
      ,(select empno 
          from (select empno 
                  from data 
                 group by empno 
                 order by count(0) desc)
        where rownum =1) b


By
Vamsi

[Updated on: Thu, 20 September 2007 12:51]

Report message to a moderator

Re: show 2 columns selecting on 1 table. [message #269109 is a reply to message #269068] Thu, 20 September 2007 13:14 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Frank wrote on Thu, 20 September 2007 11:25
That is just A query that happens to get the same output as the poster asked.
The poster however is so totally unclear in his question, there is no way you can be sure this is what he wants.
There are multiple rows with empno 102483. Which should be joined to which other row? Does there need to be row with 102483 for each row with another number?
What makes 102483 the number that needs to come in the second column? The fact that it is a duplicate empno? The fact that it is the max value? The fact that it has 6 digits?



Details, details Smile
Re: show 2 columns selecting on 1 table. [message #269110 is a reply to message #269052] Thu, 20 September 2007 13:18 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Who needs specs, just start writing code.
Re: show 2 columns selecting on 1 table. [message #269111 is a reply to message #269052] Thu, 20 September 2007 13:30 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Who needs specs, just start writing code.
Ready, Fire, AIM!
Previous Topic: [noob] mixing the result of 2 queries
Next Topic: need help
Goto Forum:
  


Current Time: Fri Mar 29 09:14:58 CDT 2024