| show 2 columns selecting on 1 table. [message #269052] |
Thu, 20 September 2007 10:30  |
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  |
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 #269063 is a reply to message #269062] |
Thu, 20 September 2007 10:56 |
 |
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 |
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 |
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 #269093 is a reply to message #269076] |
Thu, 20 September 2007 12:37 |
 |
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
|
Senior Member |
|
|
Very bad design.
Relying on employee name instead of id. 
Anyway, Does the following meet your requirementwith 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 |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
