Post reply

How to select rows based on one distinct column

  • April 18, 2014 at 2:34 pm

    #285889

    Team,

    --------------------------------------------------

    SalesOrder-ItemName-Price-Category

    -------------------------------------------------

    01-Camera-100-Electronic

    01-Memory-4GB-10-Memory

    01-Battery-5-Battery

    02-Keyboad-10-Accessories

    02-Mouse-5-Accessories

    03-CPU-300-Hardware

    03-Motherboad-400-Hardware

    From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.

    -----------------------------------

    SalesOrder-ItemName-Price-Category

    -----------------------------------

    01-Camera-100-Electronic

    02-Keyboad-10-Accessories

    03-CPU-300-Hardware

    Can anyone please provide me tsql statement for above output, thank you so much...

    CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))

    GO

    INSERT INTO Table1

    SELECT '01', 'Camera', 100, 'Electronic'

    UNION ALL

    SELECT '01', 'Memory 4GB', 10, 'Memory'

    UNION ALL

    SELECT '01', 'Battery', 5, 'Battery'

    UNION ALL

    SELECT '02', 'Keyboad', 10, 'Accessories'

    UNION ALL

    SELECT '02', 'Mouse', 05, 'Accessories'

    UNION ALL

    SELECT '03', 'CPU', 300, 'Hardware'

    UNION ALL

    SELECT '03', 'Motherboad', 400, 'Hardware'

    GO

    SELECT SalesOrder, ItemName, Price, Category

    FROM Table1

    GO

  • April 18, 2014 at 2:51 pm

    #1707194

    This should work...

    ;WITH CTE AS (

    SELECT ROW_NUMBER() OVER (Partition BY SalesOrder ORDER BY SalesOrder) AS RNum, *

    FROM Table1

    )

    SELECT SalesOrder, ItemName, Price, Category

    FROM CTE

    WHERE Rnum = 1

    See this article I wrote on different Ranking functions and how they work.. http://sqlsaga.com/sql-server/what-is-the-difference-between-rank-dense_rank-row_number-and-ntile-in-sql-server/[/url]

    Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

  • April 18, 2014 at 3:58 pm

    #1707210

    Thank You so much....

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply