0

Note I always want to return all columns and not only those that are defined as distinct I have a table lets call it Foo with these columns DeviceId, TransactionId, IndetId, Datetime, SalesId, and Quantity and some more columns

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

I had hoped that the syntax for doing this should be something like this. Telling sql to use distinct only on these columns. SELECT distinct(DeviceId, TransactionId, IndetId,Datetime, SalesId), Quantity FROM Foo

If I do SELECT distinctDeviceId, TransactionId, IndetId, Datetime, SalesId FROM Foo then I miss a lot of columns and the result will be wrong

tony
  • 37
  • 5
  • Although you could use a CTE with a windowing function to return a single row with the same values in the four columns, the row returned will be arbitrary when the remaining columns differ. The key point is there is no first or last in a relational database table, which is an unordered set of rows. – Dan Guzman Dec 29 '22 at 17:49
  • _"SELECT distinctDeviceId, ..."_ looks like a missing-space typo (after the `distinct`)? And you can use back-ticks to format the SQL code differently than the question text – Stefan Wuebbe Dec 29 '22 at 17:50

0 Answers0