How to return unique rows but in addition all columns

Transact-SQL
1

I have a table lets call it Foo with these columns DeviceId, TransactionId, IndetId, Datetime, SalesTransactionTypeId, and Quantity and some more columns

If I have rows that are identical for columns DeviceId, TransactionId, IndetId, Datetime, SalesTransactionTypeId I want to select only the first one and skip the rest.
An example
DeviceId TransactionId IndetId Datetime SalesTransactionTypeId Quantity
123 10 8 2022-12-12 700 45
123 10 8 2022-12-12 700 75
In this example only the firsr row should be returned.
I can't use a simple distinct because I need all columns to be returned

2

Hi,
Base on what criteria , you have selected that row ?
Except Quantity, the all columns are identical. So , you need a solid criteria to select the desired record.

The key point here is ORDER BY Quantity ASC used in ROW_NUMBER()

For example, I used , in this test , the criteria : Quantity ASC

CREATE TABLE Transactions
    ([DeviceId] int, [TransactionId] int, [IndetId] int, [Datetime] datetime, [SalesTransactionTypeId] int, [Quantity] int)
;
    
INSERT INTO Transactions
    ([DeviceId], [TransactionId], [IndetId], [Datetime], [SalesTransactionTypeId], [Quantity])
VALUES
    (123, 10, 8, '2022-12-12 00:00:00', 700, 45),
    (123, 10, 8, '2022-12-12 00:00:00', 700, 75)
;

SELECT
  [DeviceId], [TransactionId], [IndetId], [Datetime], [SalesTransactionTypeId], [Quantity]
FROM
  (
      SELECT * , 
            ROW_NUMBER() OVER(PARTITION BY [DeviceId], [TransactionId], [IndetId], [Datetime] 
                        ORDER BY Quantity ASC ) as rn
     FROM Transactions as t
  )as src
WHERE
  src.rn = 1

output:

DeviceId TransactionId IndetId Datetime SalesTransactionTypeId Quantity
123 10 8 2022-12-12 00:00:00.000 700 45

dbfiddle