select distinct in temporal table sql server

Asked

Viewed 225 times

1

I have the following code:
(is from a table that the representatives have the same code, which generates duplicated values, I solve this by taking the most current representative, in short the last guy who synchronized)

select distinct top $numeroRepresentantes
    t.nomerepresentante,
    t.codigorepresentante 
    from (SELECT distinct
                s.nomeRepresentante,
                s.codigorepresentante,
                datasinc
                FROM vw_Sincronismo s 
                where s.CodigoRepresentante in(102,120) 
                ORDER BY s.datasinc desc) as t

Which returns me the following error:

Message 1033, Level 15, Status 1, Line 5
Invalid ORDER BY clause in displays, embedded functions, derived tables, common sub-consultations and table expressions, unless TOP, OFFSET or FOR XML is also specified.

Sorry the ignorance I don’t know much about SQL, but I need all the results of the table to appear, if necessary, I can’t use a TOP large to solve my problems, I would like to understand what happened here

sql sql-server select order-by distinct

  • About "the last guy who synchronized", requires the last synchronization of each representative OR the last synchronization, whatever the representative?

  • I use the latest timing to ensure that he is the "current owner" of codigoRepresentant assigned to him, other representatives with the same code, I assume as "dismissed"

  • I understood about code reuse; I even changed code #1 to get only the last synchronization of each value of CodigoRepresentante . The question now is: what the query should return?

  • 1

    @Josédiz, exactly that, the last representative with the x code, I was studying his code and had already made this change that you implemented in v2, so I just marked it as the correct answer

1 answer

2


representatives have the same code (...)
catching the most current representative, in short the last guy who synchronized

If what you need is the last sync of each representative, make sure the following code meets your needs.

-- código #1 v2
with Sinc_2 as (
SELECT *, 
       seq= row_number() over (partition by CodigoRepresentante
                               order by datasinc desc)
  from vw_Sincronismo
  --where CodigoRepresentante in (102, 120)
)
SELECT nomerepresentante, codigorepresentante, datasinc 
  from Sinc_2
  where seq = 1;

The above code uses CTE (common table Expression), that facilitates the understanding and maintenance of it. See article "Modular programming with table expressions (CTE)”.

Browser other questions tagged sql sql-server select order-by distinct

You are not signed in. Login or sign up in order to post.