View previous topic :: View next topic
|
Author |
Message |
gopurs
New User
Joined: 04 Apr 2005 Posts: 13
|
|
|
|
Hi,
Please see the below data in a table. I would like to retrieve only those records from the table where there are multiple current_row value of '1'.
Can anyone suggest the query for retrieving the same in DB2.
Ref current_row
ABCD 0
ABCD 0
ABCD 1
ABCD 1
DEFG 0
DEFG 0
DEFG 0
DEFG 0
DEFG 0
MANR 0
MANR 0
MANR 0
MANR 1
MANR 1
Query Result should be :
MANR 1
MANR 1
ABCD 1
ABCD 1 |
|
Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
Gopurs,
Try this one, Its not producing what exactly you want, still doing what you want to do.
SELECT COL1, COL2, COUNT(*) FROM TABNAME GROUP BY COL1, COL2 HAVING (COUNT(*)>1 AND COL2 ='1';
This query produces results... like in your case.....
MANR 1 2
ABCD 1 2
Where 2 at the last column is the count of occurrences.
Regards,
Priyesh |
|
Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
Hi Gopur,
Try this query....Here is another version .......It'll be more helpful & as per your requirement.
SELECT COL_1, COL_2 FROM TAB_NAME
WHERE COL_1 IN
( SELECT COL_1 FROM TAB_NAME
GROUP BY COL_1, COL_2 HAVING COUNT(*)>1
)
AND COL_2 = 1;
Regards,
Priyesh. |
|
Back to top |
|
|
|