CODEWITHSUNDEEP
sqlsql-servert-sql
PJZ
PJZ

Reputation: 21

Selecting DISTINCT records on subset of columns with MAX in another column

I have been looking at other T-SQL questions including DISTINCT and MAX here on the site for a couple hours now, but cannot find anything that quite matches my need. Here is a desription of my dataset and query objectives. Any guidance is much appreciated.

Dataset Dataset is a list of customers, customer sites, dates and values from the last billing cycle, with the following columns. It is possible for a single customer to have multiple sites:

Customer, Site, Date, Counter, CounterValue, CollectorNode

Query Requirements For the given billing cycle, I would like to select the following

My challenge here is my inability to return all the columns while selecting the DISTINCT columns and MAX for each. My many varied attempts return multiple records for each customer/site combination.

Upvotes: 2

Views: 6872

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332521

Using a self JOIN:

SELECT ds.customer,
       ds.site,
       ds.counter, 
       ds.countervalue,
       ds.collectornode
  FROM DATASET ds
  JOIN (SELECT t.customer,
               t.site,
               MAX(t.countervalue) AS max_countervalue
          FROM DATASET t
      GROUP BY t.customer, t.site) x ON x.customer = ds.customer
                                    AND x.site = ds.site
                                    AND x.max_countervalue = ds.countervalue

Using a CTE & ROW_NUMBER (SQL Server 2005+):

WITH example AS (
   SELECT ds.customer,
          ds.site,
          ds.counter, 
          ds.countervalue,
          ds.collectornode,
          ROW_NUMBER() OVER(PARTITION BY ds.customer, ds.site
                                ORDER BY ds.countervalue DESC) AS rank
     FROM DATASET ds)
SELECT e.customer,
       e.site,
       e.counter, 
       e.countervalue,
       e.collectornode
  FROM example e
 WHERE e.rank = 1

Upvotes: 8

Larry Coleman
Larry Coleman

Reputation: 202

Use a subquery to do the grouping and join the result back to the original table, like this:

SELECT g.Customer, g.Site, c.Date, c.Counter, g.MaxCounterValue, c.CollectorNode
FROM Customers c
INNER JOIN
(
SELECT Customer, Site, MAX(CounterValue) MaxCounterValue
FROM Customers
GROUP BY Customer, Site
) g
ON g.Customer = c.Customer
AND g.Site = g.Site

Upvotes: 1

Related Questions