How to get five max with column name?

Question

I want maximum number of votes from these 3 columns(cong, bjp and aap) with associate column name my table is :    Cong  BJP  AAP  Winner Win By    18515      72283   23757   BJP   48526   24730   29373   36863   AAP   7490     expected result:  maxvote  Party Name  72283  BJP  36863  AAP

Vulpes · Accepted Answer

That's a tougher one but try:
SELECT ((CASE WHEN Cong >= BJP AND Cong >= AAP THEN Cong                    WHEN BJP >= AAP THEN BJP                    ELSE AAP             END) - [Win By]) AS [against party vote],
CASE WHEN BJP >= Cong AND Cong >= AAP THEN Cong                    WHEN AAP >= Cong AND Cong >= BJP) THEN Cong                    WHEN Cong >= BJP AND BJP >= AAP THEN BJP                    WHEN AAP >= BJP AND BJP >= Cong THEN BJP                    ELSE AAP          END AS [Party Name] FROM TableName

Amit Arya · Answer

Thanks sir, really help full,
now I want (maxvote-Win by) after doing that i will get the against party votes, I want that party name too
expected result:
against party vote Party Name 23757 AAP 29373 BJP

Vulpes · Answer

Try:
SELECT CASE WHEN Cong >= BJP AND Cong >= AAP THEN Cong
WHEN BJP >= AAP THEN BJP
ELSE AAP
END AS maxvote, Winner AS [Party Name] FROM TableName

Cong	BJP	AAP	Winner	Win By
18515	72283	23757	BJP	48526
24730	29373	36863	AAP	7490