Comparing the field values and return the row

[Former Member]

Hi, I have table with 3 fields

COL1, COL2 and COL3

COL3 having values 1 or 0 for all the records in a table.

Suppose COL2 having value AB which is having 1 and 0 as values of COL3 that means 2 records. from these 2 records i need to retrieve one record based on COL3 value.

in this case i need to PIck where the COL3 =1

suppose the COL2 having only one value with COL3 having 0 then i need to consider that one

Please help me how to get this in sql

[former_member186082 · Accepted solution]

Hi Kalyan,

Is this what you want?

SELECT * FROM TABLE WHERE (COL2, COL3) IN (SELECT COL2, MAX(COL3) FROM TABLE GROUP BY COL2);

Regards,

Chandu.

[Former Member · Accepted solution]

I think I didn't mentione the proper example.

suppose if COL3 having A in place of 1 and B in the place of 0 then how can we do it.

Thanks Chandra.

[Former Member · Accepted solution]

Hi Kalyan,

I hope this might help you ..

Shirish.

[Former Member · Accepted solution]

Thanks  a lot for your valuable suggestions.

I got this in the query using window function.