How to retrieve mutliple current rows

gopurs · New User Joined: 04 Apr 2005 Posts: 3

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

priyesh.agrawal

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

priyesh.agrawal

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.