View previous topic :: View next topic
|
Author |
Message |
noorkh
New User
Joined: 06 Mar 2006 Posts: 76 Location: Chennai
|
|
|
|
Hi,
I have below requirements.
My table name. TDMTABLE
There are 2 columns say. TDM_POL_KEY and TDM_THG_KEY.
I would like to find out (is there 2 THG_KEY found for same POL_KEY).
For eg:
----------------------------
POL_KEY | THG_KEY |
----------------------------
21 | 101 |
21 | 101 |
22 | 102 |
22 | 103 |
23 | 104 |
23 | 104 |
-----------------------------
In the above table keys, for POL_KEY 22, i have 2 different THG_KEY 102 and 103.
Can any one let me know the query to find out such POL_KEY? |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
For your example the query wud be
Code: |
SELECT TDM_POL_KEY FROM (
SELECT TDM_POL_KEY,COUNT(DISTINCT TDM_THG_KEY) AS TCOUNT
FROM TDMTABLE
GROUP BY TDM_POL_KEY) AS TEMP WHERE TCOUNT > 1;
|
Quote: |
I would like to find out (is there 2 THG_KEY found for same POL_KEY).
|
you need to find out if there exists only 2 diff keys and not less ??
wht is the o/p required if the data is
----------------------------
POL_KEY | THG_KEY |
----------------------------
21 | 101 |
22 | 102 |
22 | 103 |
23 | 104 |
----------------------------- |
|
Back to top |
|
|
nabarundas
New User
Joined: 21 Jun 2007 Posts: 28 Location: pune
|
|
|
|
Hi noorkh,
Try the following query
select pol_key from TDMTABLE
group by pol_key
having count(distinct thg_key) > 1
Regards,
Nabarun |
|
Back to top |
|
|
|