Select Distinct returns repeated data when I use Row_Number

Question

Select Distinct returns repeated data when I use Row_Number

Navigation

#1 by (3 votes)

3

I'm trying to make paging with SQLServer 2008 as follows:

WITH CTEResults AS
(
    SELECT DISTINCT
    SolicitacaoServico.AutoId       AS Id_Solicitacao,
    Pessoa.Nome                     AS Nome,
    Beneficiario.Codigo             AS Codigo_Beneficiario,
    SolicitacaoServico.Codigo       AS Cod_Solicitacao,
    ServicoOperadora.Codigo         AS Cod_Servico,
    ServicoOperadora.Nome           AS Servico,
    CONVERT (INTEGER, ItemSolServico.QteSolicitada) AS Qtd_Solicitada,
    CONVERT (VARCHAR, SolicitacaoServico.DataSolicitacao, 103) AS Dt_Solicitacao,
    CONVERT (VARCHAR, SolicitacaoServico.DataSolicitacao, 108) AS Hora,
    TransacaoSolicitacao.Atendente,

    CASE WHEN (SolicitacaoServico.CanalSolicitacao IN ('90')) THEN
             'HILUM' 
    ELSE
        CASE WHEN (SolicitacaoServico.CanalSolicitacao IN ('1')) THEN
             'BALCÃO' 
    ELSE         
              'OUTROS'    
            END
        END AS Canal_Solicitacao,
        TipoParSolServico.Nome AS Parecer,
        TipoSituacaoSolServico.Nome AS Situacao,
        CONVERT (VARCHAR, SolicitacaoServico.TelosUpDt, 103) AS Dt_Final,
        CONVERT (VARCHAR, SolicitacaoServico.TelosUpDt, 108) AS Hora_Final,
        ROW_NUMBER() OVER (ORDER BY SolicitacaoServico.AutoId) AS RowNum


FROM
    SolicitacaoServico  WITH (NOLOCK)
    INNER JOIN ItemSolServico       WITH (NOLOCK) ON ItemSolServico.Solicitacao = SolicitacaoServico.AutoId
    INNER JOIN ServicoOperadora     WITH (NOLOCK) ON ItemSolServico.Servico = ServicoOperadora.AutoId
    INNER JOIN TransacaoSolicitacao WITH (NOLOCK) ON TransacaoSolicitacao.SolicitacaoServico = SolicitacaoServico.AutoId
    LEFT JOIN TipoParSolServico     WITH (NOLOCK) ON SolicitacaoServico.Parecer = TipoParSolServico.Codigo
    LEFT JOIN TipoSituacaoSolServico    WITH (NOLOCK) ON SolicitacaoServico.Situacao = TipoSituacaoSolServico.Codigo
    INNER JOIN Beneficiario         WITH (NOLOCK) ON SolicitacaoServico.Beneficiario = Beneficiario.AutoId
    INNER JOIN Pessoa               WITH (NOLOCK) ON Beneficiario.Pessoa = Pessoa.AutoId 

WHERE 
    SolicitacaoServico.DataSolicitacao BETWEEN  ('2016-01-01 00:00:00.000') AND (GETDATE())
    AND Beneficiario.Codigo     LIKE    '117%'
    AND Beneficiario.Tipo       NOT IN  ('9')
    AND SolicitacaoServico.Codigo = 43568861

)
SELECT Distinct *
FROM CTEResults
WHERE RowNum BETWEEN 1 AND 40;

But the return comes with 5x more data. When I do this same query with only the first Select and without this excerpt: ROW_NUMBER() OVER (ORDER BY SolicitacaoServico.AutoId) AS RowNu only two lines are returned, however if I do the way I posted 10 lines are returned.

I think that Disctint is not working when I use it in conjunction with Row_Number() , How could this problem be solved?

sql sql-select

asked by anonymous 12.02.2016 / 15:03

1 answer

How to delete methods automatically generated by Netbeans? Binary Search Tree - PrintInOrder ();

score 3 · Accepted Answer

You're right in your assumption. It is exactly the ROW_NUMBER that is outputting this way. Note that you make DISTINCT , but ROW_NUMBER is producing a sequence of 1 to n records. Since everything is distinct between 1 and n , the output produces the 10 registers.

One way to solve this is to change the SQL as follows. Note that SQL is normally done without RowNum , and on top of that SQL, another is done by querying the fields and placing a ROW_NUMBER for each one. Finally, the expected (with both records) is produced, and on top of that dataset the ROW_NUMBER is placed.

WITH CTEResults AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY query.Id_Solicitacao) AS RowNum FROM (
            SELECT DISTINCT
            SolicitacaoServico.AutoId       AS Id_Solicitacao,
            Pessoa.Nome                     AS Nome,
            Beneficiario.Codigo             AS Codigo_Beneficiario,
            SolicitacaoServico.Codigo       AS Cod_Solicitacao,
            ServicoOperadora.Codigo         AS Cod_Servico,
            ServicoOperadora.Nome           AS Servico,
            CONVERT (INTEGER, ItemSolServico.QteSolicitada) AS Qtd_Solicitada,
            CONVERT (VARCHAR, SolicitacaoServico.DataSolicitacao, 103) AS Dt_Solicitacao,
            CONVERT (VARCHAR, SolicitacaoServico.DataSolicitacao, 108) AS Hora,
            TransacaoSolicitacao.Atendente,

            CASE WHEN (SolicitacaoServico.CanalSolicitacao IN ('90')) THEN
                       'HILUM' 
            ELSE
                  CASE WHEN (SolicitacaoServico.CanalSolicitacao IN ('1')) THEN
                       'BALCÃO' 
            ELSE         
                        'OUTROS'    
                      END
                  END AS Canal_Solicitacao,
                  TipoParSolServico.Nome AS Parecer,
                  TipoSituacaoSolServico.Nome AS Situacao,
                  CONVERT (VARCHAR, SolicitacaoServico.TelosUpDt, 103) AS Dt_Final,
                  CONVERT (VARCHAR, SolicitacaoServico.TelosUpDt, 108) AS Hora_Final


            FROM
                    SolicitacaoServico  WITH (NOLOCK)
                    INNER JOIN ItemSolServico       WITH (NOLOCK) ON ItemSolServico.Solicitacao = SolicitacaoServico.AutoId
                    INNER JOIN ServicoOperadora     WITH (NOLOCK) ON ItemSolServico.Servico = ServicoOperadora.AutoId
                    INNER JOIN TransacaoSolicitacao WITH (NOLOCK) ON TransacaoSolicitacao.SolicitacaoServico = SolicitacaoServico.AutoId
                    LEFT JOIN TipoParSolServico     WITH (NOLOCK) ON SolicitacaoServico.Parecer = TipoParSolServico.Codigo
                    LEFT JOIN TipoSituacaoSolServico    WITH (NOLOCK) ON SolicitacaoServico.Situacao = TipoSituacaoSolServico.Codigo
                    INNER JOIN Beneficiario         WITH (NOLOCK) ON SolicitacaoServico.Beneficiario = Beneficiario.AutoId
                    INNER JOIN Pessoa               WITH (NOLOCK) ON Beneficiario.Pessoa = Pessoa.AutoId 

            WHERE 
                    SolicitacaoServico.DataSolicitacao BETWEEN  ('2016-01-01 00:00:00.000') AND (GETDATE())
                    AND Beneficiario.Codigo     LIKE    '117%'
                    AND Beneficiario.Tipo       NOT IN  ('9')
                    AND SolicitacaoServico.Codigo = 43568861
    ) query
)
SELECT *
FROM CTEResults
WHERE RowNum BETWEEN 1 AND 40;