Remove second line or the same number in SQL Server

sql, sql-server, sql-server-2008, t-sql

Solution

Since the problem was tag with `SQL Server 2005+`, you can use `Common Table Expression` and `Window Function` on this.

WITH recordsList
AS
(
    SELECT  A.CustomerNo, A.INV_Nr, B.Code, A.Price ,
            ROW_NUMBER() OVER (PARTITION BY A.CustomerNo
                                ORDER BY B.Code DESC) rn
    FROM    INVOICE A
            INNER JOIN INVOICE_LINE B
                ON A.IVC_Nr = B.IVC_Nr
)
SELECT  CustomerNo, INV_Nr, Code, Price
FROM    recordsList
WHERE   rn = 1

- SQLFiddle Demo (with slight changes)

Problem

I'm using SQL Server. And I used this code: ``` select distinct A.CustomerNo, A.INV_Nr, B.Code, A.Price from INVOICE A. INVOICE_LINE B where A.IVC_Nr = B.IVC_Nr ``` The output is like this: ``` | CustomerNo | INV_Nr | Code | Price | ================================================= | 1100021 | 500897 | 1404 | 2500 | | 1100021 | 500897 | 1403 | 2500 | | 1100022 | 500898 | 1405 | 3500 | | 1100023 | 500899 | 1405 | 3000 | | 1100023 | 500899 | 1403 | 3000 | ``` How can I remove the second line and get only the 1st line of the same number and should be like this: ``` | CustomerNo | INV_Nr | Code | Price | ================================================= | 1100021 | 500897 | 1404 | 2500 | | 1100022 | 500898 | 1405 | 3500 | | 1100023 | 500899 | 1405 | 3000 | ``` Thanks,

Original source