Need help creating priority-sorting using one column to return data

Database
Highlighted
Enthusiast

Need help creating priority-sorting using one column to return data

Hi all,

I am having some trouble selecting rows based on priority for one column

Here is snapshot of my table:

Screen Shot 2017-11-21 at 6.36.27 PM.png

I want to select all records for each CID based on priority of SrcID column. So for each CID, all Community numbers must be selected but should not be repeated. The priority for SrcID is 

150

25

100. 

So for CID 1000, below records should be selected:

Screen Shot 2017-11-21 at 6.41.05 PM.png

Can you please help me here? I am clueless here.

 

 


Accepted Solutions
Junior Contributor

Re: Need help creating priority-sorting using one column to return data

You need to apply a ROW_NUMBER partitoned CID, Community. This should return the expected result:

 

qualify
   row_number()
   over (partition by CID, Community 
         order by case SrcID 
                    when 150 then 1
                    when  25 then 2
                    when 100 then 3
                    else 4
                  end) = 1 

 

 

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Need help creating priority-sorting using one column to return data

You need to apply a ROW_NUMBER partitoned CID, Community. This should return the expected result:

 

qualify
   row_number()
   over (partition by CID, Community 
         order by case SrcID 
                    when 150 then 1
                    when  25 then 2
                    when 100 then 3
                    else 4
                  end) = 1 

 

 

 

Enthusiast

Re: Need help creating priority-sorting using one column to return data

Thanks dnoeth

That worked perfectly.