CODEWITHSUNDEEP
sqlt-sqlselectsql-server-2008-r2
Archangel33
Archangel33

Reputation: 461

Select top N rows from table Column containing N

TSQL 2008 R2

I have duplicate line items each with their own Unique ID for a given PO#. I Need to DELETE the duplicates. I don't know how many duplicates I have for each PO#. Because There is a #LineItems I know I only want the first(top) x(#lineIems) line items.

I have a table like so:

LineitemID PO#     #LineItems
---------- ------- ----------
11111111   12345   3
22222222   12345   3
33333333   12345   3
44444444   12345   3
55555555   12345   3
66666666   12345   3
77777777   54321   2
88888888   54321   2
99999999   54321   2
10101010   54321   2
...

I need a table like this:

LineitemID PO#     #LineItems
---------- ------- ----------
11111111   12345   3
22222222   12345   3
33333333   12345   3
77777777   54321   2
88888888   54321   2
...

So basically is there a way to do something like this:

DECLARE @top int = (SELECT TOP 1 FROM tblLineItems t WHERE t.PO# = @PO)

SELECT TOP (@top)
FROM tblLineItems

And do this per each PO#

Upvotes: 3

Views: 286

Answers (2)

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Deleted first answer after facepalm.

The only issue with your example is that you didn't ORDER BY in the TOP query, which could pull the results out in any order. This query assumes you are ordering by the LineItemID (integer type) ASC.

DECLARE @LineItems TABLE
(
    LineItemID INT
    , PO INT
    , NumLineItems INT
)

INSERT INTO @LineItems (LineItemID, PO, NumLineItems)
SELECT 11111111,   12345,   3
UNION ALL SELECT 22222222,   12345,   3
UNION ALL SELECT 33333333,   12345,   3
UNION ALL SELECT 44444444,   12345,   3
UNION ALL SELECT 55555555,   12345,   3
UNION ALL SELECT 66666666,   12345,   3
UNION ALL SELECT 77777777,   54321,   2
UNION ALL SELECT 88888888,   54321,   2
UNION ALL SELECT 99999999,   54321,   2
UNION ALL SELECT 10101010,   54321,   2

DELETE b
FROM
(
    SELECT *
        , RANK() OVER (PARTITION BY PO, NumLineItems ORDER BY LineItemID ASC) AS r
    FROM @LineItems
) a
JOIN @LineItems b
    ON a.LineItemID = b.LineItemID
WHERE r > a.NumLineItems

SELECT *
FROM @LineItems
ORDER BY PO, LineItemID

Upvotes: 2

bwperrin
bwperrin

Reputation: 692

delete t from (select *, rank = row_number() over (partition by PO# order by lineitemid) 
               from table ) t 
where rank > #lineitems

Upvotes: 2

Related Questions