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
  

Poston Fri, 01 Feb 2008 10:50 am

I have a table of States (UF) and other cities.

Code: Select all
SELECT COUNT(*)
FROM TBLCIDADES
WHERE UF = 'PR'
with this select I can know how many cities have the PR.

Now I wonder a select to bring the State possessing the largest amount cities.
Hernani
Location: PR

Poston Fri, 01 Feb 2008 11:03 am

And ai Hernani, beauty?

There follows an example..

Code: 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!!
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Fri, 01 Feb 2008 11:24 am

And aí Tineks, beauty.

just do not understand the last line of your code WHERE ROWNUM = < 1 \"this ROWNUM
Hernani
Location: PR

Poston Fri, 01 Feb 2008 12:02 pm

Brother, This just brings you ROWNUM, row 1.
As is ordained DESC, it automatically brings you the greatest value.
Do a test, remove this line and see what happens.

qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Fri, 01 Feb 2008 12:07 pm

must be particularity of oracle, sorry if it's there. I'm using mysql I entered in this forum because here things are faster. Thank you very much. good holidays to m. ..
Hernani
Location: PR

Poston Fri, 01 Feb 2008 12:12 pm

This same brother.
More specifically a PSEUDOCOLUMN.
As well as ROWID, DUAL ... so on.. lol ...
qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Fri, 01 Feb 2008 12:41 pm

Maybe there's something in mySQL like Sql Server.. in sql server the equivalent command to rownum is the TOP, in case TOP 1 brings only the first line, top 2 the first 2 and so on ... :D

[]s'!!!
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Wed, 06 Feb 2008 9:00 am

in mysql uses LIMIT
Code: Select all
$sql LIMIT 0,15 -- RETORNA 15 primeiros resultados
$sql LIMIT 16,15 --- RETORNA os proximos 15 resultados a contanto com o 16
:-.
alef
Location: Patos de Minas - MG

Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

Poston Fri, 08 Feb 2008 1:15 pm

DO THAT IT'S GOING TO WORK SELECT SA.A1_EST, (SELECT COUNT (A1.A1_MUN) FROM SA1010 WHERE A1 A1.D_E_L_E_T _ <> ' * ' AND A1.A1_EST = SA.GROUP BY A1_EST A1.A1_MUN) FROM SA1010 WHERE SA SA.D_E_L_E_T _ <> ' * ' GROUP BY SA.A1_EST
ALEX
Location: DIADEMA,SP



Return to SQL

Who is online

Users browsing this forum: No registered users and 5 guests