CODEWITHSUNDEEP
sqlsql-server
Mo na
Mo na

Reputation: 1

SQL Command to show no duplicates

I am trying to answer this question: Which are the top 10 states with the most Medicare Payment in 2014? This is what I have so far:

enter image description here

I am trying to get rid of the duplicate States. There should be 10 different states that have the highest Medicare Payment.

Upvotes: 0

Views: 141

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

Let's say that you want the "highest" average per state to be the defining characteristic for the ordering.

We can do that by constructing a CTE or subquery that runs a GROUP BY and aggregate to reduce each state to a single row, before attempting to find the top 10:

;With Highest as (
    select [Provider State],MAX([Average Medicare Payments]) as Medicare
    from [Medicare_InPatient_2014]
    group by [Provider State]
)
select top 10 *
from Highest
order by Medicare desc

Upvotes: 2

Related Questions