Retrieve some data with another condition, while the table being grouped by

Question

SELECT * FROM sales;

Hi,

The initial table looks like that. Now I want to get a data that has a bigger sales_amt. For example, for first 2 rows for sales_rep_id with 150, sales_id 41 has bigger value than second row. Then I want to get the first row.

Next, For 3rd and 4th lines, sales_id 23 has a bigger value than sales_id 33.

It's working like that.

I'm thinking of doing like this cause I need to see the results by sales_rep_id

SELECT MAX(sales_amt), sales_rep_id FROM sales GROUP BY sales_rep_id

What else do I need to do more?

I want to show sales_rep_id, MAX(sales_amt), sales_cust_id as well

Thanks in advance.

Answer 1

If you use rank analytic function, you can partition rows by sales_rep_id and sort them by sales_amt in descending order. Then - as a result - return only rows that rank as the highest.

Something like this:

with temp as
  (select sales_id,
          sales_rep_id,
          sales_cust_id,
          sales_amt,
          rank() over (partition by sales_rep_id order by sales_amt desc) rnk
   from sales
  )
select t.sales_id,
       t.sales_rep_id,
       t.sales_cust_id,
       t.sales_amt
from temp t 
where t.rnk = 1;