Remove second row or same number in SQL Server
I am 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 looks 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 only get the 1st line of the same number and should look like this:
| CustomerNo | INV_Nr | Code | Price |
=================================================
| 1100021 | 500897 | 1404 | 2500 |
| 1100022 | 500898 | 1405 | 3500 |
| 1100023 | 500899 | 1405 | 3000 |
Thank,
+3
source to share
1 answer
Since the problem was with the tag SQL Server 2005+
, you can use Common Table Expression
and Window Function
to do so.
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
+6
source to share