CODEWITHSUNDEEP
sql-server
Ropeh
Ropeh

Reputation: 43

Get distinct records where column is highest

I have a Table of columns of which I would like to get the highest dollar amount for each distinct (month) column. Is there a simple way to capture the records in SQL Server?

Original Table

ID  ProjID  Month   Spent
1   14  Oct  $11,846.00 
1   6   Oct  $82,000.00 
2   8   Nov  $6,051,645.00 
2   9   Nov  $408,744.00 
3   7   Dec  $165,827.00 
3   6   Dec  $485,160.00 
4   1   Jan  $10,300.00 
4   7   Jan  $249,656.00

Desired Results

ID  ProjID  Month   Spent

1   6   Oct  $82,000.00 
2   8   Nov  $6,051,645.00 
3   6   Dec  $485,160.00 
4   7   Jan  $249,656.00

The following query I tried displays all records.

SELECT
    [MAX_T].[ID],
    [MAX_T].[ProjID],
    [MAX_T].[Month],
    [MAX_T].[MaxSpent]
FROM
    @Table1 [T] INNER JOIN
    (SELECT [ID], [ProjID], [Month], MAX([Spent]) AS [MaxSpent]
    FROM @Table1
    GROUP BY [ID], [ProjID], [Month]) AS [MAX_T]
    ON [T].[ProjID] = [MAX_T].[ProjID] AND
       [T].[Month] = [MAX_T].[Month] AND
       [T].[Spent] = [MAX_T].[MaxSpent]

Upvotes: 1

Views: 69

Answers (3)

tember
tember

Reputation: 1496

This is how I did it:

select ID, ProjID, Month, Spent, RowNo 
from (
    select ID, ProjID, Month, Spent,
        ROW_NUMBER ()
        over (
            PARTITION BY Month
            order by Spent DESC
            ) as RowNo 

    from (
        select ID, ProjID, Month, Spent
        from Table1
    )
    where RowNo = 1
)

This is untested code - I just substituted your field names for mine.

Upvotes: 0

xQbert
xQbert

Reputation: 35323

Another way is to consider the data in sets:

1st get a set with the highest dollar per month then join that set back to the base set to get the addtional data...

SELECT A.* 
FROM @TABLE A
INNER JOIN (
  SELECT Month, Max(Spent) as MSpent
  FROM @Table1
  GROUP BY Month) B
on B.Month = A.Month, B.MSpent = A.Spent

Upvotes: 0

Lamak
Lamak

Reputation: 70638

Use ROW_NUMBER:

;WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Spent DESC)
    FROM dbo.YourTable
)
SELECT  ID,
        ProjID,
        [Month],
        Spent
FROM CTE
WHERE RN = 1;

Upvotes: 2

Related Questions