SQL to bring the state that has more cities

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
Hernani
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Fri, 01 Feb 2008 10:41 am
Location: PR

I have a table of states (UF) and one of cities.

Select all

SELECT COUNT(*) 
FROM TBLCIDADES 
WHERE UF = 'PR'
With this select I can know how many cities have in PR.

Now I would like to know a Select to bring the state that has the largest quantities cities.
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

And then hernani, beauty ??

Here is an example.

Select all

 
SELECT * 
  FROM (SELECT   COUNT (1) qt, a.estado 
            FROM tab_cidades a 
        GROUP BY a.estado 
        ORDER BY 1 DESC) 
 WHERE ROWNUM <= 1 
[] 's !!
Hernani
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Fri, 01 Feb 2008 10:41 am
Location: PR

And Tineks, beauty.

I just did not understand the last line of your code WHERE ROWNUM <= 1
this rownum
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother,

This rownum, only brings you 1 line.
As you are ordered Desc, it automatically brings you the highest value.
Take a test, remove this line and see what happens.

Anything, send it there.
Hernani
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Fri, 01 Feb 2008 10:41 am
Location: PR

It must be Oracle's particularity, sorry if it is. It's just that I'm using MySQL I entered this forum because the cosies are here faster. Thank you very much. Good holidays to the garela ....
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

That's right brother.
more specifically a pseudocoluna.
Just like rowid, dual ... there goes .. rs ...
whatever, send it there.
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

Tantves in mysql exists something similar to the sql server .. in sql server the command equivalent to rownum is the top, in the top case 1 brings only the first line, top 2 the first 2 and so will ..: D

[] S '!!!
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

In MySQL uses limit




: -o


]].
ALEX
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Fri, 08 Feb 2008 12:42 pm
Location: DIADEMA,SP

Do this it will work

Select all

SELECT SA.A1_EST, 
 
	(SELECT COUNT(A1.A1_MUN) FROM SA1010 A1 
	WHERE A1.D_E_L_E_T_ <> '*' 
	AND A1.A1_EST = SA.A1_EST 
	GROUP BY A1.A1_MUN) 
 
 
 FROM SA1010 SA 
WHERE SA.D_E_L_E_T_ <> '*' 
GROUP BY SA.A1_EST
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 17 guests