CODEWITHSUNDEEP
sql-servert-sqlpartitioning
Etienne
Etienne

Reputation: 7201

RANK() Over Partition BY not working

When I run the code below the ROWID is always 1. I need to the ID to start at 1 for each item with the same Credit Value.

;WITH CTETotal AS (SELECT
     TranRegion
    ,TranCustomer
    ,TranDocNo
    ,SUM(TranSale) 'CreditValue'
FROM dbo.Transactions

LEFT JOIN customers AS C 
      ON custregion = tranregion 
      AND custnumber = trancustomer
LEFT JOIN products AS P
      ON prodcode = tranprodcode

GROUP BY
TranRegion
,TranCustomer
,TranDocNo)

SELECT
       r.RegionDesc
      ,suppcodedesc
      ,t.tranreason as [Reason]
      ,t.trandocno as [Document Number]
      ,sum(tranqty) as Qty
      ,sum(tranmass) as Mass
      ,sum(transale) as Sale
      ,cte.CreditValue AS 'Credit Value'
      ,RANK() OVER (PARTITION BY cte.CreditValue ORDER BY cte.CreditValue)AS ROWID

FROM transactions t

LEFT JOIN dbo.Regions AS r    
      ON r.RegionCode = TranRegion  

LEFT JOIN CTETotal AS cte
      ON cte.TranRegion = t.TranRegion
      AND cte.TranCustomer = t.TranCustomer
      AND cte.TranDocNo = t.TranDocNo

GROUP BY 
       r.RegionDesc
      ,suppcodedesc
      ,t.tranreason
      ,t.trandocno
      ,cte.CreditValue

ORDER BY CreditValue ASC

EDIT

All the credit values with 400 must have the ROWID set to 1. And all the credit values with 200 must have the ROWID set to 2. And so on and so on.

Upvotes: 0

Views: 9071

Answers (5)

tylersDisplayName
tylersDisplayName

Reputation: 1643

Edit: The issue here isn't actually the nesting of the subquery, it's potentially based on partition by having columns that truly make each row unique (or 1)

Rather than ranking within your complex query like this

select 
   rank() over(partition by...),
   *
from
   data_source
join table1
join table2
join table3
join table4
order by
   some_column

Try rank() or row_number() on the resulting data set, not within it.

For example, using the query above, remove rank() and implement it this way:

select
   rank() over(partition by...),
   results.*
from (
    select 
       *
    from
       data_source
    join table1
    join table2
    join table3
    join table4
    order by
       some_column
) as results

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Do you need something like this?

with cte (item,CreditValue)
as
(
select 'a',8 as CreditValue union all
select 'b',18 union all
select 'a',8 union all
select 'b',18 union all
select 'a',8 
) 
select CreditValue,dense_rank() OVER (ORDER BY item)AS ROWID from cte

Result

CreditValue ROWID
----------- --------------------
8           1
8           1
8           1
18          2
18          2

In your code replace 

,RANK() OVER (PARTITION BY cte.CreditValue ORDER BY cte.CreditValue)AS ROWID

by

,DENSE_RANK() OVER (ORDER BY cte.CreditValue)AS ROWID

Upvotes: 5

Sergei Rogovtcev
Sergei Rogovtcev

Reputation: 5832

You just don't have to use PARTITION, just DENSE_RANK() OVER (ORDER BY cte.CreditValue)

Upvotes: 4

AnandPhadke
AnandPhadke

Reputation: 13496

Try this

RANK() OVER (PARTITION BY cte.CreditValue ORDER BY cte.RegionDesc)AS ROWID

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

I think the problem is with the RANK() OVER (PARTITION BY clause

you have to partition it by item not by CreditValue

Upvotes: 0

Related Questions