3 ms·

Nicer formatting of OPs woes: SELECT sd1.sale_person_id, sd1.sale_person_name, sd1.no_products_sold, sd1.commission_perc

Nicer formatting of OPs woes: SELECT sd1.sale_person_id, sd1.sale_person_name, sd1.no_products_sold, sd1.commission_percentage, sd1.sales_department FROM sales_department_details sd1 INNER JOIN ( SELECT sale_person_id, MAX(no_products_sold) AS no_products_sold FROM sales_department_details GROUP BY sale_person_id ) sd2 ON sd1.sale_person_id = sd2.sale_person_id AND sd1.no_products_sold = sd2.no_products_sold;

wodenokoto 4y ago
With fear of syntax errors, I propose the following: WITH max_sold_per_id AS ( SELECT sale_person_id, MAX(no_products_sold) AS no_products_sold FROM sales_department_details GROUP BY sale_person_id ) SELECT sd1.sale_person_id, sd1.sale_person_name, sd1.no_products_sold, sd1.commission_percentage, sd1.sales_department FROM sales_department_details AS sd1 INNER JOIN max_sold_per AS sd2 ON sd1.sale_person_id = sd2.sale_person_id AND sd1.no_products_sold = sd2.no_products_sold; Which still isn't nice to "bang out" in an exploratory analysis. Especially because the thinking goes "Get the max sales numbers and associated IDs, and then add the meta data", but the code is still "with the meta data, join the max sales data" regardless whether you prepare the max sales data "at the top" (which I did) or in the join (as OP did) In dplyr, which imho has a nice syntax (but tons of other problems) it would be: sales_department_details %>% group_by(sale_person_id) %>% filter(no_products_sold == max(no_products_sold)) %>% select( sale_person_id, sale_person_name, no_products_sold, commission_percentage, sales_department ) First we group by ID, then on each group, we filter for the maximum sales, and finally we select the columns we are interested in. Super easy to bang out (and probably inefficient as we are moving tons of unnecessary columns between statements, but that is not the syntax fault)

wodenokoto 4y ago

With fear of syntax errors, I propose the following: WITH max_sold_per_id AS ( SELECT sale_person_id, MAX(no_products_sold) AS no_products_sold FROM sales_department_details GROUP BY sale_person_id ) SELECT sd1.sale_person_id, sd1.sale_person_name, sd1.no_products_sold, sd1.commission_percentage, sd1.sales_department FROM sales_department_details AS sd1 INNER JOIN max_sold_per AS sd2 ON sd1.sale_person_id = sd2.sale_person_id AND sd1.no_products_sold = sd2.no_products_sold; Which still isn't nice to "bang out" in an exploratory analysis. Especially because the thinking goes "Get the max sales numbers and associated IDs, and then add the meta data", but the code is still "with the meta data, join the max sales data" regardless whether you prepare the max sales data "at the top" (which I did) or in the join (as OP did) In dplyr, which imho has a nice syntax (but tons of other problems) it would be: sales_department_details %>% group_by(sale_person_id) %>% filter(no_products_sold == max(no_products_sold)) %>% select( sale_person_id, sale_person_name, no_products_sold, commission_percentage, sales_department ) First we group by ID, then on each group, we filter for the maximum sales, and finally we select the columns we are interested in. Super easy to bang out (and probably inefficient as we are moving tons of unnecessary columns between statements, but that is not the syntax fault)