Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Hernani
Rank: Estagiário Pleno
Posts: 7 Joined: Fri, 01 Feb 2008 10:41 am
Location: PR
Fri, 01 Feb 2008 10:50 am
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
Posts: 365 Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Fri, 01 Feb 2008 11:03 am
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
Posts: 7 Joined: Fri, 01 Feb 2008 10:41 am
Location: PR
Fri, 01 Feb 2008 11:24 am
And Tineks, beauty.
I just did not understand the last line of your code WHERE ROWNUM <= 1
this rownum
Trevisolli
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
Fri, 01 Feb 2008 12:02 pm
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
Posts: 7 Joined: Fri, 01 Feb 2008 10:41 am
Location: PR
Fri, 01 Feb 2008 12:07 pm
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
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
Fri, 01 Feb 2008 12:12 pm
That's right brother.
more specifically a pseudocoluna.
Just like rowid, dual ... there goes .. rs ...
whatever, send it there.
Tineks
Rank: DBA Sênior
Posts: 365 Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Fri, 01 Feb 2008 12:41 pm
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 '!!!
alef
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
Wed, 06 Feb 2008 9:00 am
In MySQL uses limit
: -o
]].
ALEX
Rank: Estagiário Júnior
Posts: 1 Joined: Fri, 08 Feb 2008 12:42 pm
Location: DIADEMA,SP
Fri, 08 Feb 2008 1:15 pm
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
Users browsing this forum: No registered users and 17 guests