How to get the first result of duplicate lines?

Question

Situation:

In a table there is a list of items with their respective values invoiced in invoice. This table has a single field that varies according to the insertion of the data, can repeat all other columns and does not have a PK. An example would be:

fk_nota_fiscal | fk_produto | valor | campo2 | campoXXX
244060         | 0010101    | 100,00| X      | A       
244060         | 0010101    | 100,00| X      | B     
244060         | 0010101    | 100,00| X      | C     

Desired:

I need to get only the first result regardless of the variation of the column campoXXX.

Problem encountered:

Using the functions ROW_NUMBER() and OVER(), I grouped the results into one CTE and I was able to select only the first result, but the performance is absurdly slow and unviable for a considerable amount of records.

ROW_NUMBER() OVER(PARTITION BY fk_nota_fiscal,fk_produto ORDER BY fk_nota_fiscal,fk_produto) AS ROWID

SELECT * FROM FROM CTE_vwItensRefaturar WHERE ROWID = 1

Answer 1

You can use a sub select with top 1 to do this along with the group by do select,

select fk_nota_fiscal,  fk_produto, valor,  campo2,
(select top 1 campoXXX from @teste t2 
        where t2.fk_nota_fiscal = t1.fk_nota_fiscal and t2.fk_produto = t1.fk_produto) 
from @teste t1
group by fk_nota_fiscal,    fk_produto, valor,  campo2

Or use the function FIRST_VALUE sql server, but you still have to use a sub select.

Select * from
    (
        SELECT   fk_nota_fiscal,
                 fk_produto,
                 valor,
                 campo2,
                 FIRST_VALUE(campoXXX) OVER (ORDER BY fk_nota_fiscal ASC) AS campoXXXX   
        FROM     @teste
    ) d
    GROUP BY fk_nota_fiscal,
             fk_produto,
             valor,
             campo2,
             campoXXXX

A solution, I don’t know if feasible for your structure, would be the creation of a table (mirror) of your table, as this you could perform your query to make the desired data Inserts and a TRIGGER in your original table to update this table when there is change, with this you could make the select right in your table(mirror).

Answer 2

Have you tried this? (This syntax may vary depending on the database, this is for sql server)

SELECT TOP 1 fk_nota_fiscal, fk_produto, valor, campo2, campoXXX
FROM table_name;

Or distinct?

SELECT DISTINCT fk_nota_fiscal, fk_produto, valor, campo2
FROM table_name;