Select Distinct Single Item then any item from other columns in MS SQL 2012
Account Home | Help | Blog | Contact us | Log Out

Home | Toolbox | Software Development | Website Development | SEO | Portfolio | Quotation | Articles | Resources | About Us | Contact  

Welcome to Kbytes > Articles

Select Distinct Single Item then any item from other columns in MS SQL 2012

Posted By: siteadmin on 18/10/2012 12:29:00


Perennial problem which you would imagine would be easy to do, but always seems to cause confusion.

Look at the table below, we want to Select Distinct Single Item then any item from other columns in MS SQL 2012

ID shortcode latitude longitude propercasetown propercasecounty
52 A0A 47.739416 -53.229699 Victoria Cb  Newfoundland and Labrador
247 A0G 49.34863 -54.515678 Victoria Cove  Newfoundland and Labrador
14464 B1N 46.241215 -60.162584 Victoria Mines  Nova Scotia  
14473 B1N 46.182943 -60.132272 Victoria Mines  Nova Scotia  
35920 C0A 46.214371 -63.489633 Victoria  Prince Edward Island
90082 E7P 46.315401 -67.460012 Victoria Corner  New Brunswick  
799361 V9E 48.51839 -123.430466 Victoria  British Columbia  
799362 V9E 48.504644 -123.409585 Victoria  British Columbia  
             

So for example here we want just take one V9E row, not both. Similary for the B1N row.

The SQL 2012 command for this gleaned from the for MS-SQL answer on http://dba.stackexchange.com/questions/24327/how-to-select-distinct-for-one-column-and-any-in-another-column

 

;with FinalDataset as
(
    select *,
        row_number() over(partition by shortcode order by propercasetown ) as rownum
    from NETCANADA_postcodes_premiumdb
)
select
   propercasetown,
   shortcode
from FinalDataset 
where rownum = 1
blog comments powered by Disqus

Kbytes Home | Privacy Policy | Contact us | Testing Area

© 2004 - 2024 1 Oak Hill Grove Surbiton Surrey KT6 6DS Phone: +44(020) 8123 1321