-1

enter image description here

This question is best explained with an image and the script I have currently... How can I extract a FULL one row per assignment, with the lowest rank, and if there are 2 rows with a denserank as 1, then choose either of them?... 

select *
,Dense_RANK() over (partition by [Assignment] order by [Text] desc) as 
[DenseRank]
 from  [dbo].[CLEANSED_T3B_Step1_Res_Withdups____CP]




select * from
(
select *
,Dense_RANK() over (partition by [Assignment] order by [Text] desc, NewID() 
) as [DenseRank] from  [dbo].[CLEANSED_T3B_Step1_Res_Withdups____CP]
 ) as A
 where A.[DenseRank] = 1

Second script is working perfectly!

SELECT * INTO 
[dbo].[CLEANSED_T3B_Step1_COMPLETED]
from
(
  select *
    ,Dense_RANK()                   over (partition by [Assignment] order by 
 left([Text],1) desc , [Diff_Doc_Clearing_Date] desc , [Amount] asc
as [DenseRank] 
 from  [dbo].[CLEANSED_T3B_Step1_Res_Withdups____CP] 
 )  
as A
 where A.[DenseRank] = 1

No longer need just a random first Tied '1st place', now need to get the one with the highest day diff and then also the highest amount after. SO have adapted everything in this version 3.

user4242750
  • 187
  • 1
  • 3
  • 13

2 Answers2

2

It seems you don't want to use DENSE_RANK but ROW_NUMBER.

with cte as(
   select t.*, rn = row_number() over(partition by assignment order by [text] desc) 
   from tablename t
)
select * from cte 
where rn = 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • I am now trying with your method as I can no longer just take any 'Tied' 1st places on the rank (Using the random NewID(). I need to take the 1 with the highest [Diff_Doc_Clearing_Date]. Regards, James – user4242750 Jul 13 '17 at 08:38
1

Order by 'newid()' as the 'tie-breaker'

Order by [Text],Newid()
  • select * from ( select * ,Dense_RANK() over (partition by [Assignment] order by [Text] desc, NewID() ) as [DenseRank] from [dbo].[CLEANSED_T3B_Step1_Res_Withdups____CP] ) as A where A.[DenseRank] = 1 – user4242750 Jul 12 '17 at 14:45