Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

Extracting distinct information


I need to extract from this database Item_no,sku,item_desc the problem is that I have repeating sku_no, for some of the items, so I need to find the repeating sku_no’s and extract only one combination of  Item_no,sku,item_desc,
If you look at the sku_no=140314 I am getting 7 instances and I need only one.


item_no      item_desc                                                                            sku_no
162911      DOGWOOD FLORIDA WHITE FLWR TREE 25%           140065              
162850      DOGWOOD KOUSA FLWR TREE                       140152              
162851      DOGWOOD KOUSA FLWR TREE 25%                   140193              
162402      CRABAPPLE ADAMS PINK FLWR TREE                140314              
162409      CRABAPPLE CENTURION RED FLWR TREE             140314              
162411      CRABAPPLE INDIAN MAGIC FLWR TREE              140314              
162413      CRABAPPLE HARVEST GLD WHITE FLWR TR           140314              
162415      CRABAPPLE ROBINSON FLWR TREE                  140314              
162420      CRABAPPLE SNOWDRIFT FLWR TREE                 140314              
162400      CRABAPPLE FLWR TREE ASSORTED                  140314              
162401      CRABAPPLE FLWR TREE ASSORTED 25%              140319              
1608100      CRABAPPLE FLWR TREE ASSORTED                  140860              
468865      CHERRY KWANZAN FLWR TREE                      142260              
468866      CHERRY KWANZAN FLWR TREE 25%                  142468              
468860      CHERRY WEEPING FLWR TREE                      143622              
468861      CHERRY WEEPING FLWR TREE 25%                  144187              
0
GadFriedman
Asked:
GadFriedman
  • 3
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
select * from
(
select *, row_number() over(partition by sku_no order by sku_no) Serial from yourtable
) a where Serial = 1

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
ROW_NUMBER() which is available from SQL SERVER 2005 + helps you put a 'Serial' number based on partition we mention.

Here we are fetching all records which is having Serial as 1

http://thehobt.blogspot.com/2009/02/rownumber-rank-and-denserank.html
0
 
x-menIT super heroCommented:
select distinct ...
0
 
GadFriedmanAuthor Commented:
Thank you
0
 
Rajkumar GsSoftware EngineerCommented:
Welcome
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now