Need help to make a Better SQL query.. I need to display name of SM_NAME wit highest commission.

Question

SQL> select sm_name from sales_man_details where sm_id=(select sm_id from s_commission where commiss
ion=(select max(commission) from s_commission));

SQL> select * from s_commission;

SM_ID COMMISSION
---------- ----------
S1 400
S2 600
S3 600

SQL> select * from sales_man_details;

SM_ID SM_NAME
---------- --------------------------------------------------
S1 Mahesh
S2 Vijay
S3 Surya

my query wrkd..bt nd a bttr one.

Answer 1

SELECT A.SM_NAME, B.COMMISSION FROM sales_man_details A LEFT JOIN s_commission B ON A.AM_ID = B.SM_ID ORDER BY B.COMMISSION DESC LIMIT 1

Answer 2

SELECT sales_man_details.sm_name, sales_man_details.sm_id
FROM sales_man_details
INNER JOIN(
SELECT max(s_commission.commission),
s_commission.sm_id FROM s_commission
GROUP BY s_commission.sm_id) ON s_commission.sm_id = sales_man_details.sm_id

If your query works, why do you need a better one?

Answer 3

SELECT smd.SM_NAME FROM sales_man_details smd
INNER JOIN s_commission sc
ON smd.SM_ID = sc.SM_ID
WHERE sc.COMMISSION = (SELECT MAX(COMMISSION) FROM s_commission)

Answer 4

my query looks bulky
wish to learn whethr it can b done in a better
way
thank u for replying