Extracting distinct information

Posted on 2011-10-20
Last Modified: 2012-08-13

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              
Question by:GadFriedman
    LVL 23

    Accepted Solution

    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

    LVL 23

    Expert Comment

    by:Rajkumar Gs
    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
    LVL 18

    Expert Comment

    select distinct ...

    Author Closing Comment

    Thank you
    LVL 23

    Expert Comment

    by:Rajkumar Gs

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now