CODEWITHSUNDEEP
sqlt-sql
Sanjna Malpani
Sanjna Malpani

Reputation: 175

SELECT TOP N with distinct/unique field values

Based on the answer to a previous question of mine. I end up with a result set something like:

PartyName   Risk    SubTotal    Total   

A           High    100         280
B           Med     25          45
A           Low     30          280
A           Med     70          280
B           Low     10          45
C           High    110         170
C           Med     60          170
D           Low     30          30
A           Med     80          280
B           Low     10          45

What I need is to SELECT TOP N unique PartyName with highest Amounts, i.e if N = 2 the result should be:

PartyName   Risk    SubTotal    Total   

A           High    100         280
A           Low     30          280
A           Med     70          280
C           High    110         170
C           Med     60          170
A           Med     80          280

all entries with the highest N Total values.

Tried this:

SELECT DISTINCT TOP(10) s.PartyName, s.Risk, s.SubTotal, s2.Total
FROM 
    (SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL)) SubTotal
    FROM CustomerData
    GROUP BY PartyName, Risk) S
LEFT JOIN
    (SELECT PartyName, SUM(CAST(Amount AS DECIMAL)) Total
    FROM CustomerData
    GROUP BY PartyName) S2 
ON S.PartyName = S2.Partyname

But doesn't work

Upvotes: 1

Views: 999

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I think this version should do what you want:

SELECT TOP (10) WITH TIES PartyName, Risk,
       SUM(CAST(Amount AS DECIMAL)) as SubTotal,
       MAX(SUM(CAST(Amount AS DECIMAL))) OVER (PARTITION BY PartyName) as Total
FROM CustomerData
GROUP BY PartyName, Risk
ORDER BY Total DESC, PartyName;

EDIT:

The above gives all rows tied with the 10th. If you want all rows with the 10 distinct values, then let's use DENSE_RANK():

SELECT cd.*
FROM (SELECT cd.*, DENSE_RANK() OVER (ORDER BY Total DESC) as seqnum
      FROM (SELECT TOP (10) WITH TIES PartyName, Risk,
                   SUM(CAST(Amount AS DECIMAL)) as SubTotal,
                   MAX(SUM(CAST(Amount AS DECIMAL))) OVER (PARTITION BY PartyName) as Total
            FROM CustomerData
            GROUP BY PartyName, Risk
           ) cd
     ) cd
WHERE seqnum <= 10
ORDER BY Total DESC, PartyName;

Upvotes: 0

Jesse
Jesse

Reputation: 873

Off the top off my head, maybe something like this:

if object_id('tempdb.dbo.#test') is not null drop table #test
create table #test
(
    partyname varchar(50),
    Risk varchar(50), 
    amount int
)

insert into #test
select 'A','High',50
union all select 'B','Med',  15
union all select 'A','Low',   12
union all select 'A','Med' ,  43
union all select 'B','Low' ,   65
union all select 'C','High',    12
union all select 'C','Med' ,   789
union all select 'D','Low' ,   12
union all select 'A' ,'Med',    34
union all select 'B' ,'Low',    43

SELECT
    main.PartyName, 
    main.Risk, 
    main.SubTotal, 
    TotalValues.Total 
FROM
    --get party+risk+subtotal
    (
            SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL)) SubTotal
            FROM #test
            GROUP BY PartyName, Risk 
     ) main
--get total by partyname with a rownum to get top N, where N=2
INNER JOIN 
    (SELECT
        b.partyName, b.Total, row_number() over (order by Total desc) as rid
    FROM 
            (
                SELECT b.PartyName, SUM(CAST(Amount AS DECIMAL)) as Total  
                FROM #test b
                group by b.PartyName
            )  as b
    ) as TotalValues
    on TotalValues.partyName = main.partyName
    and TotalValues.rid <= 2 --n = 2
order by 
    main.partyname,
    TotalValues.Total

Upvotes: 1

xQbert
xQbert

Reputation: 35323

1st we get a set of data with the totals, next we find the range of totals of interest, finally we get teh results...

Untested:

WITH mAgg AS (SELECT partyName
                  , Risk
                  , sum(cast(amount as decimal(10,2)) over (partition by partyName, Risk) as subTotal
                  , sum(cast(amount as decimal(10,2)) over (partition by partyName) as Total
             FROM CustomerData),

  mRange as (SELECT distinct top 2 total from mAgg order by total desc)


SELECT * FROM mAgg where Total >=  (SELECT min(total) 
                                   FROM mRange))

Or maybe we could just dense_rank() over (partition by total desc) and then get the anything with rank >=2 or N...

Upvotes: 0

Related Questions