|
View previous topic :: View next topic
|
| Author |
Message |
technut
New User
.jpg)
Joined: 27 Dec 2007
Posts: 73
Location: India
|
|
|
|
Hi,
Lets assume that a table has below fields and all of them part of primary key. For e.g.. The structure of table like below.
empseq empname empgrade
1 aaa Y
1 bbb Y
2 aaa Y
1 aaa z
I want to select those records from this table with duplicate emp names but unique empseq and empgrade combination. Now the
result of my query should return below,
1 aaa Y
2 aaa Y
Can the query be written without any joins on the same table?
Please let me know if I am not clear enough..Thanks! |
|
| Back to top |
|
 |
knn9413
New User
Joined: 23 Jul 2009
Posts: 17
Location: US
|
|
|
|
| do a group by on those columns with a having clause > 1... that should work... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Can the query be written without any joins on the same table? |
| Quote: |
| do a group by on those columns with a having clause > 1... |
To do this, i suspect there will be a self-join? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
You could use "where exists".
but I am not sure of your requirements :
why would it return
1 aaa Y
2 aaa Y
and not return
1 aaa Z
? |
|
| Back to top |
|
|
technut
New User
Joined: 27 Dec 2007
Posts: 73
Location: India
|
|
|
|
Apologize!!! I framed the it a little wrong. The sequence number will get incremented only when both name and grade are same. Hence I wanted those dupliacte employee names with the same grade but unique Grade and Req combination.
If possible, can you explain on the solution employing where exists? Thanks.. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select * from tab1 A
where exists(select * from tab1 B
where A.grade = B.grade
and A.name = B.name
and A.seq <> B.seq) |
|
|
| Back to top |
|
|
|
|
