How to show only a result of a specific column [MSSQL]

Question

I have the following QUERY:

 select id_cc_ponto_saldo_vecto,
           id_planta_re,
           tbl_cc_ponto_saldo_vecto.re,
           ponto_saldo_atual,
           convert(varchar(10),vecto_ponto,121)
      from tbl_cc_ponto_saldo_vecto INNER JOIN tbl_usuario aa ON aa.re = tbl_cc_ponto_saldo_vecto.re 
     where venceu = '2'
               and id_planta_re in ('100','110')
       and ponto_saldo_atual > 0
       and aa.status = '1'
       and (zerou is null or zerou = 'N')

Where the result is similar to the line below:

4803 100 100363 801.00 NULL

8202 110 100363 40.00 NULL

The problem is that it repeats the "RE" (bold number). What I would like is to show only one line, no matter the line that will show, provided it shows 1 result for each "RE"

It is possible?

Thank you

Answer 1

In this case, you need to use the GROUP BY clause, which groups the result by a parameterization

The rules may change depending on your SGBP, but in general terms it would look like this:

 select MAX(id_cc_ponto_saldo_vecto) AS id_cc_ponto_saldo_vecto,
           MAX(id_planta_re) AS id_planta_re,
           tbl_cc_ponto_saldo_vecto.re,
           MAX(ponto_saldo_atual) AS ponto_saldo_atual,
           convert(varchar(10),MAX(vecto_ponto),121)
      from tbl_cc_ponto_saldo_vecto INNER JOIN tbl_usuario aa ON aa.re = tbl_cc_ponto_saldo_vecto.re 
     where venceu = '2'
               and id_planta_re in ('100','110')
       and ponto_saldo_atual > 0
       and aa.status = '1'
       and (zerou is null or zerou = 'N')
group by id_cc_ponto_saldo_vecto, id_planta_re, tbl_cc_ponto_saldo_vecto.re, ponto_saldo_atual, vecto_ponto

You can read more about group by here: https://www.w3schools.com/sql/sql_groupby.asp

The above example included the MAX command to be compatible with SQL Server.

SQL Server GROUP BY clause documentation: https://msdn.microsoft.com/pt-br/library/ms177673(v=sql.120). aspx

Another solution! Would do with sub-select in WHERE using EXISTS, since your JOIN you use for filter:

SELECT
    id_cc_ponto_saldo_vecto,
    id_planta_re,
    re,
    ponto_saldo_atual,
    CONVERT(VARCHAR(10), vecto_ponto, 121) AS vecto_ponto
FROM
    tbl_cc_ponto_saldo_vecto
WHERE
    (venceu = '2')
    AND (id_planta_re in ('100','110'))
    AND (ponto_saldo_atual > 0)
    AND EXISTS (
      SELECT
        aa.re
      FROM
        tbl_usuario AS aa
      WHERE
        (aa.re = tbl_cc_ponto_saldo_vecto.re)
        AND (aa.status = '1')
    )
    AND (
      (zerou IS null)
      OR (zerou = 'N')
    );

Documentation of EXISTS: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql