select yearofelection,con_code, con_name, name,VotesPolled, status from Election_history_Assembly E where 1= (select count(distinct votespolled) from Election_history_Assembly eh where e.yearofelection=eh.yearofelection and e.status=eh.status and e.con_code= eh.con_code and e.votespolled<=eh.votespolled) These is the result: yearofelection con_code con_name name VotesPolled status 1983 1 ICHAPURAM M.V. KRISHNA RAO 28168 WON 1983 1 ICHAPURAM S.L. RAO 19062 LOST 1983 2 SOMPETA M. NARAYANA RAO 31314 WON 1983 2 SOMPETA GOUTHU LACHANNA 27271 LOST 1983 3 TEKKALI A. JANARDHAN RAO 35274 WON 1983 3 TEKKALI S.L. NAIDU 15558 LOST i want the result in the form is: 1983 1 ICHAPURAM M.V. KRISHNA RAO 28168 WON S.L. RAO 19062 LOST 1983 2 SOMPETA M. NARAYANA RAO 31314 WON GOUTHU LACHANNA 27271 LOST
So you have a separate record for who won, and who lost, and you want to see the two together on a single line in the results? The standard solution for this is to use a self-join, which is a join between two instances of the same table. SELECT t1.colA, t2.colA FROM MyTable t1 INNER JOIN MyTable t2 ON t1.key = t2.key WHERE t1.Status = 'LOST' AND t2.Status = 'WON' After ON, you put all the fields where the two rows must match ("yearofelection" and "con"). After WHERE, you put all the fields where the two rows must be different ("status").