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

Question

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

Navigation

#1 by (0 votes)

0

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 looks like the line below:

4803 100 100363 801.00 NULL

8202 110 100363 40.00 NULL

The problem is that it repeats the "RE" (number in bold). What I would like was for you to display only one line, no matter what line you are going to show, as long as you show 1 result for each "RE"

Is it possible?

Thank you

sql-server

asked by anonymous 16.08.2017 / 23:21

1 answer

sum recursive sequence php Page, Do not Save On The Bank! Because?

score 0 · Accepted Answer

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: link

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

SQL Server GROUP BY clause documentation: link

Another solution! It would do with sub-select in WHERE using EXISTS, since your JOIN you use to 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')
    );

EXISTS documentation: link