CODEWITHSUNDEEP
sqloracle-database
user7977107
user7977107

Reputation: 11

Need to retrieve different records but

I want to retrieve data without duplicated records ..the problem is that the duplicated records have similar values in all columns except a specific column called seq_id so i can not use distinct ... My select must have seq_id ...this is the code

SELECT x.seq_id,
       y.name,
       x.user,
       x.time_1,
       y.acc_num
FROM table1 y,
     table2 x
WHERE y.c_id=x.m_id;

The result looks like this

╔════════════════════════════════════════════════════╗
║ Seq_id      |name      |user      |time     |acc_n ║
╠════════════════════════════════════════════════════╣
║ 100         jim.        Ali.      10:11:30  0098   ║
║ 101         jim.        Ali.      10:11:30  0098   ║
║ 102         john        sam       04:19:30  0097   ║
╚════════════════════════════════════════════════════╝

I want it to be like 

╔═════════════════════════════════════════╗
║ Seq_id |name  |user   |time      |acc_n ║
╠═════════════════════════════════════════╣
║ 100    jim.   Ali.    10:11:30   0098   ║
║ 102    john   sam     04:19:30   0097   ║
╚═════════════════════════════════════════╝

Upvotes: 1

Views: 42

Answers (3)

Shahram
Shahram

Reputation: 56

select x.seq_id, y.name, x.user1, x.time_1, y.acc_num
from table20 x, table21 y
where x.seq_id = y.seq_id
and x.seq_id = (select max(x1.seq_id)
from table20 x1, table21 y1
where x1.seq_id = y1.seq_id
and y.name = y1.name 
and x.user1 =x1.user1
and x.time_1=x1.time_1
and y.acc_num=y1.acc_num)

Upvotes: 0

Utsav
Utsav

Reputation: 8093

First of all, please avoid using old join syntax. Use INNER JOINinstead.

Now apart for the group by as answered by APC, you can also use Analytical function to achieve the same. 

Select * from 
 (SELECT x.seq_id,
       y.name,
       x.user,
       x.time_1,
       y.acc_num,
       Row_number() over (partition by  
       y.name, x.user, x.time_1,y.acc_num 
       order by x.seq_id) as rno 
 FROM 
  table1 y
    INNER JOIN
  table2 x 
    ON y.c_id=x.m_id
) where rno=1

Upvotes: 1

APC
APC

Reputation: 146239

"It does not matter which [seq_id] to choose ..i just want one of them"

In which case simple aggregation will solve this for you.

SELECT min(x.seq_id) as seq_id,
       y.name,
       x.user,
       x.time_1,
       y.acc_num
FROM table1 y,
     table2 x
WHERE y.c_id=x.m_id
group by y.name,
       x.user,
       x.time_1,
       y.acc_num;

Upvotes: 5

Related Questions