0

I found many related topics here but nothing that really addressed my problem.

SalesTable:

PersonID | SaleAmount | SaleDate | TransactionID
6117 | 10 | 2013-03-13 | 1
6117 | 10 | 2013-03-11 | 2
6400 | 100 | 2013-03-12 | 3
6400 | 10 | 2013-02-05 | 4
6525 | 0 | 2013-03-14 | 5
6400 | 0 | 2013-04-01 | 6

I only want the highest sale amount transaction from each person. If all transactions from same person has same sale amount then return latest one by date.

Expected result:

PersonID | SaleAmount | SaleDate | TransactionID
6117 | 10 | 2013-03-13 | 1
6400 | 100 | 2013-03-12 | 3
6525 | 0 | 2013-03-14 | 5

I was able to group by person with highest sale no problem but couldnt get the latest transaction if all sale amount was same from one person.

select personid, max(saleamount) from SalesTable group by personid

This would be wrong

select personid, max(saleamount), max(saledate) 
from SalesTable 
group by personid

because it would give me the wrong date for 6400

6117 | 10 | 2013-03-13 | 1
6400 | 100 | 2013-04-01 | 3
6525 | 0 | 2013-03-14 | 5
Aaron Kurtzhals
  • 2,036
  • 3
  • 17
  • 21
user2188129
  • 3
  • 2
  • 1
    Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it – Taryn Mar 19 '13 at 20:13

1 Answers1

1

Try:

with cte as
(select s.*, 
        rank() over (partition by PersonID 
                     order by SaleAmount desc, SaleDate desc) rn
 from SalesTable s)
select PersonID, SaleAmount, SaleDate, TransactionID
from cte
where rn=1