select distinct in temp table sql server

Question

select distinct in temp table sql server

Navigation

#1 by (2 votes)

1

I have the following code: (it is from a table that the representatives have the same code, which generates duplicate values, I solve this by picking the most current representative, in short the last face that synchronized) / em>

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

That returns me the following error:

Message 1033, Level 15, State 1, Line 5 The ORDER BY clause is invalid in views, built-in functions, derived tables, common subqueries and table expressions, unless TOP, OFFSET, or FOR XML is also specified.

Sorry for the ignorance I do not understand a lot of SQL, but I need all the results of the table to appear, if necessary, I can not use a TOP numerogrande to solve my problems, I would like to understand what happened here

sql sql-server select order-by distinct

asked by anonymous 04.10.2018 / 21:41

1 answer

How to show queries generated by EF core Show all contents of span independent of size [duplicate]

score 2 · Accepted Answer

The representatives have the same code (...)
taking the most current representative, in short the last face that synchronized

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

-- 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 code above uses CTE (common table expression ), which makes it easier to understand and maintain it. See article " Modular Programming with Table Expressions (CTE) / a> ".