I apply the SELECT DISTINCT across multiple columns. Where the values ​​of the rows of two or more columns are different, doubles appear (one column each).

Here is what I mean, see the picture: enter image description here Doubles are not needed! Of course, you can then go through the sample in a loop and replace all the values ​​in the column with NULL .. But, (processing code in PL), this can be very slow!

And here, how to write such a SQL query right away, which will show only one first value of a row in a column, and the rest duplicate values ​​of rows will make NULL ?? In SQL

PS Of course, ideally it should work on several columns too ..

  • one first row value by column The concept of the first value appears only after the sorting criterion, by which these same doubles are unique, appears. While I do not observe such a criterion (and generally mentioning sorting). - Akina
  • one
    This should be done not in the database, but already during data output. Suppressing duplicate values ​​only makes sense on the screen, and you should not think about the screen output of the database. Therefore, SQL is not designed to solve such problems. It certainly is certainly solved, but usually very cumbersome and / or slow. It's easy enough to get rid of if firebird supports the lag () window function - Mike

2 answers 2

It is better to do this when outputting data, but if you really really want ...

 SELECT NULLIF(t.field1,LAG(t.field1) OVER(ORDER BY t.field1,t.field2)), t.field2 FROM table1 AS t ORDER BY t.field1,t.field2

LAG, like other window functions, is available starting with Firebird 3.0.

    In general, if you look at it, grouping by this column can help, deleting an existing table and inserting it in its place. See examples, there are other approaches. https://toster.ru/q/90425
    If nothing helps at all, but you want to do it with SQL tools, look towards PL / SQL. Write a function.