Taylor D Taylor D - 2 months ago 7
SQL Question

SQL return distinct results and a column that does not have to be distinct

So I have a table with a bunch of columns but I only need 5 columns returned from the table they are:

Name | Charge Code | Charge Amount | Modifier | Date

I am looking to return the fields based on only Distinct Name, Charge Codes, Charge Amounts and Modifiers. The Date can be the latest Date that the record exists. How can I do this?

Currently I have:

Select Name, Charge_code, Charge_amount, Modifier, Date
From Table1
group by Name, Charge_code, Charge_amount, Modifier


However when I do this it tells me the Date is invalid in the select list because it is not contained in either an aggregate function or group by. I Don't want to include the Date in the group by function cause then it will return a bunch of extraneous rows that are not necessary. But I do want the date to show the latest date that that person charged that amount.

I have also tried

Select Date, Distinct Name, Charge_Code, Charge_amount, Modifier
From Table 1


Any ideas would be helpful I am new.
Thanks

Answer

You can try this as well, simple and easy

Select name, 
    charge_code, 
    charge_amount, 
    modifier, 
    max(date) date
From table1
Group by name, charge_code, charge_amount, modifier