locked
Select Distinct with Select Max RRS feed

  • Question

  • User1122355199 posted

    Hello everyone and thanks for your help in advance.  I need to write a query against a table tblProductPayments that returns each distinct ProductCode like:

    Select Distinct ProductCode from tblProducts

    Then return the MAX PaidAmt from the same table for each ProductCode.  I'm not sure how to do this.  Any help would be appreciated.

    Tuesday, August 16, 2016 1:53 AM

Answers

  • User-595703101 posted

    Hi,

    We can use SQL aggregate functions with Partition By clause as follows

    select
    Distinct 
    ProductCode, 
    MAX(PaidAmt) OVER (Partition By ProductCode) MaxPaidAmt
    from tblProductPayments 

    This will enable you to list products and maximum paid amount on product bases

    I hope it helps,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 16, 2016 5:53 AM
  • User1122355199 posted

    Perfect.  Exactly what I needed.  Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 17, 2016 4:59 PM

All replies

  • User-595703101 posted

    Hi,

    We can use SQL aggregate functions with Partition By clause as follows

    select
    Distinct 
    ProductCode, 
    MAX(PaidAmt) OVER (Partition By ProductCode) MaxPaidAmt
    from tblProductPayments 

    This will enable you to list products and maximum paid amount on product bases

    I hope it helps,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 16, 2016 5:53 AM
  • User77042963 posted
    select 
    ProductCode, 
    MAX(PaidAmt) as MaxPaidAmt
    from tblProductPayments
    Group by ProductCode

    Tuesday, August 16, 2016 1:26 PM
  • User1122355199 posted

    Perfect.  Exactly what I needed.  Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 17, 2016 4:59 PM