CODEWITHSUNDEEP
sqlsql-server
Taylor D
Taylor D

Reputation: 53

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

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

Upvotes: 0

Views: 48

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

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

Upvotes: 1

Sparrow
Sparrow

Reputation: 2583

SELECT distinct  main.Name, main.ChargeCode, main.ChargeAmount, main.Modifier,  
                 b.Date
FROM Table1  as main
Outer Apply (Select Top 1 Date
            from Table1 as sub
            where main.Name = sub.Name and main.ChargeCode = sub.ChargeCode and   main.ChargeAmount = sub.ChargeAmount and main.Modifier = sub.Modifier
            Order by sub.Date desc
             ) b

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269583

The typical way is to use row_number():

select Name, ChargeCode, ChargeAmount, Modifier, Date
from (select t.*,
             row_number() over (partition by Name, ChargeCode, ChargeAmount, Modifier
                                order by date desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions