0

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

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.

18th Jan 2017, 7:12 PM
Neeraj Babu
Neeraj Babu - avatar
4 Réponses
+ 2
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
19th Jan 2017, 6:52 AM
bem
bem - avatar
+ 1
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?
18th Jan 2017, 7:22 PM
Louis Milotte
Louis Milotte - avatar
+ 1
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)
19th Jan 2017, 6:30 AM
lowshuen
0
my query looks bulky wish to learn whethr it can b done in a better way thank u for replying
18th Jan 2017, 7:57 PM
Neeraj Babu
Neeraj Babu - avatar