How to get the first result of duplicate rows?

Question

How to get the first result of duplicate rows?

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

Situation :

In a table there is a list of items with their invoiced invoice amounts. This table has a single field that varies according to the insertion of the data, being able to repeat all the 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 campoXXX column.

Problem encountered:

Using the functions ROW_NUMBER() and OVER() , I aggregated the results into a CTE and managed to select only the first result, but performance is absurdly slow and unfeasible 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

sql-server

asked by anonymous 30.08.2016 / 20:48

2 answers

1

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;

30.08.2016 / 21:08

How to sort by average and quantity (weight)? Align form items with CSS

score 2 · Accepted Answer

You can use a sub select with top 1 to do this along with 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 FIRST_VALUE function of sql server, but you'll 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

One solution, I do not know if feasible for your structure, would be to create a table (mirror) of your table, as this you could run your query to make the desired data inserts and a TRIGGER in your original table to update this table when there is change, so you could make the select direct in your table (mirror).