Doubt with query in SQL Server

Question

Doubt with query in SQL Server

Navigation

#1 by (2 votes)

0

I have the following query in SQL Server, to return me a list of cities that I need.

select IDCidade as "ID",

       case UF when 'RS' then '1'
       when 'SC' then '2'
       when 'PR' then '3'
       when 'SP' then '4'
       when 'RJ' then '5'
       when 'MG' then '6'
       when 'ES' then '7'
       when 'BA' then '8'
       when 'MS' then '9'
       when 'MT' then '10'
       when 'GO' then '11'
       when 'DF' then '12'
       when 'TO' then '13'
       when 'AM' then '14'
       when 'AC' then '15'
       when 'RO' then '16'
       when 'RR' then '17'
       when 'PA' then '18'
       when 'AP' then '19'
       when 'MA' then '20'
       when 'PI' then '21'
       when 'RN' then '22'
       when 'CE' then '23'
       when 'SE' then '24'
       when 'AL' then '25'
       when 'PB' then '26'
       when 'PE' then '27'
       else '28' end as "ESTADO",

       case Nome when 'CidadeNome' then 'Não Informado'
       else UPPER(LEFT(Nome,1))+LOWER(SUBSTRING(Nome,2,LEN(Nome))) end as "NOME"

from cidades

where IDCidade in ( select IDCidade from empresas )

However, this listing is showing me some duplicate city values, which were entered wrong in the bank. I want to know how to return the values of this query, without there being duplicate city names. I tried to use a distinct in to the Name field but it kept the same amount.

What is the correct way to do this query?

sql sql-server query sql-server-2012

asked by anonymous 13.12.2016 / 14:32

1 answer

Display values of SQLite table fields in the Console Where can I download the hump library? [closed]

score 2 · Accepted Answer

If there are repeated locations, then it seems to me that the first step is to remove them from the table. To find the possible cases of repetition in the same unit of the federation, the code below can be useful.

-- código #1
with LocalidadeUF as (
SELECT Nome, UF, IDCidade,
       Qtd= Count(*) over (partition by lower(Nome), lower(UF))
  from cidades
)
SELECT Nome, UF, IDCidade
  from LocalidadeUF
  where Qtd > 1
  order by lower(Nome), lower(UF);

With the result of the query, carefully evaluate the cases indicated as repetition. After, just delete the lines that you consider repeated. Of course this depends on whether the city code is in use or not in another table (referential integrity).

After cleaning, simply re-run the code you transcribed.

But if you can not delete the repeated lines, follow the contour solution.

-- código #2
with cidades2 as (
SELECT Nome, UF, min(IDCidade) as IDCidade
  from cidades
  group by lower(Nome), lower(UF)
)
SELECT IDCidade as "ID",
       case UF when 'RS' then '1'
       when 'SC' then '2'
       ...
       when 'PE' then '27'
       else '28' end as "ESTADO",

       case Nome when 'CidadeNome' then 'Não Informado'
       else UPPER(LEFT(Nome,1))+LOWER(SUBSTRING(Nome,2,LEN(Nome))) end as "NOME"
  from cidades2
  where IDCidade in (SELECT distinct IDCidade from empresas);

In this case the IDCity to be considered will be the lowest used for duplicate lines.