Select Distinct returns repeated data when using Row_number

Question

I’m trying to make pagination 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 that passage: ROW_NUMBER() OVER (ORDER BY SolicitacaoServico.AutoId) AS RowNu only two lines are returned, but if I do the way I posted 10 lines are returned.

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

Answer 1

You’re right in your assumption. It’s exactly the ROW_NUMBER that is producing output that way. Note that you make a DISTINCT, but the ROW_NUMBER is producing a sequence of 1 to n records. How everything is distinguished between 1 and n, output produces the 10 records.

One way to resolve is to change the SQL in the way below. Note that SQL is normally done without the RowNum and, on top of this SQL, another is done by consulting the fields and placing a ROW_NUMBER for each one. Finally, first the dataset 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;