Query with SQL Server query

Asked

Viewed 65 times

0

I have the following query in SQL Server, to return me a list of cities 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 values of duplicated cities, which were entered wrong in the bank. I want to know how to return the values of this query, without duplicate city names. I tried to use a distinct in the field Name but he kept the same amount.

What is the correct way to do this query?

sql database sql-server query sql-server-2012

  • Are you sure there are repeated denominations for localities? Remember that in Brazil there are several homonymous localities (usually in different states).

1 answer

2


If there are repeated localities, then it seems to me that the first step is to remove them from the table. To find possible cases of repetition in the same unit of the federation, the code below may 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);

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

After cleaning the data, just re-export the code you transcribed.

But if repeated lines are not allowed to be deleted, follow outline 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 smallest used, for duplicate lines.

Browser other questions tagged sql database sql-server query sql-server-2012

You are not signed in. Login or sign up in order to post.