Sql Select, Specific Rows Based On Multiple Conditions?

Not sure if it's because I'm tired, but I can't seem to figure this out... I'm looking for a Query that will filter the data based on a couple items... Sample Data: Business_Month

Solution 1:

;WITH x AS 
(
  SELECT [Business_Month], ID, Calls, Transferred, Loaded,
    rn =ROW_NUMBER() OVER 
    (PARTITIONBY ID, [Business Month] ORDERBY Loaded DESC)
  FROM dbo.yourtable
)
SELECT [Business Month], ID, Calls, Transferred, Loaded
FROM x
WHERE rn =1ORDERBY ID, [Business Month];

Solution 2:

You can use a subquery to get the max(loaded) value for each business_month and then join that back to yourtable to get the desired result:

select t1.Business_Month,
  t1.ID,
  t1.Calls,
  t1.Transferred,
  t1.Loaded
from yourtable t1
inner join
(
  select Business_Month, 
    max(Loaded) MaxLoaded
  from yourtable
  groupby Business_Month
) t2
  on t1.Business_Month = t2.Business_Month
  and t1.Loaded = t2.MaxLoaded
orderby t1.id, t1.business_month;

See SQL Fiddle with Demo