CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Captain16
Captain16

Reputation: 327

Remove the multiple transactions and remain the first transaction

I'm using SQL Server 2005 for query, This my Query:

  select Transaction_Nr, Serial_Nr, Qty

  from TRANSACTION_TABLE

and I have an output like this:

    Transaction_Nr  |   Serial_Nr |    Qty
    -------------------------------------------
      40001         |    25001    |   2000  
      40002         |    25002    |   1500
      40003         |    25003    |   2700
      40004         |    25001    |   1500
      40005         |    25002    |   1000  
      40006         |    25008    |   3000

So how can I get only the first transaction_Nr for the Serial_Nr with multiple transactions and the output should be like this:

    Transaction_Nr  |   Serial_Nr |    Qty
    -------------------------------------------
      40001         |    25001    |   2000  
      40002         |    25002    |   1500
      40003         |    25003    |   2700
      40006         |    25008    |   3000

Thanks,

Upvotes: 0

Views: 118

Answers (2)

David-SkyMesh
David-SkyMesh

Reputation: 5171

You wrote So how can I get which I interpretted to mean select:

Do a group by "Serial_Nr" to get the first Transaction_Nr for each Serial_Nr. Then join those 'first serial numbers' back on to your table to get the desired rows.

SELECT y.* 
FROM (
        SELECT MIN("Transaction_Nr") AS "Transaction_Nr" 
        FROM "TRANSACTION_TABLE"
        GROUP BY "Serial_Nr"
     ) x
INNER JOIN "TRANSACTION_TABLE" y ON y."Transaction_Nr" = x."Transaction_Nr"
ORDER BY y."Transaction_Nr";

edit: @Damien_The_Unbeliever did basically the same thing, but used a CTE (Common table expression) rather than a self-join.

On the other hand, if you meant that after your query, the table should only contain the first row (based on serial number, i.e: delete the non-first rows):

DELETE FROM "TRANSACTION_TABLE"
USING (
          SELECT "Serial_Nr" AS "ser", 
                 MIN("Transaction_Nr") AS "trn"
          FROM "TRANSACTION_TABLE" 
          GROUP BY "Serial_Nr"
      ) x
WHERE "Serial_Nr" = x.ser
AND "Transaction_Nr" != x.trn;

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

You can use ROW_NUMBER() to assign suitable ordering information:

; With Ordered as (
    select Transaction_Nr,Serial_Nr,Qty,
           ROW_NUMBER() OVER (PARTITION BY Serial_Nr
                              ORDER BY Transaction_Nr) as rn
    from TRANSACTION_TABLE
)
select Transaction_Nr,Serial_Nr,Qty
from Ordered
where rn = 1

Upvotes: 2

Related Questions