SQL - Contador Para registros Agrupados

Por favor. ¿ Alguien sabría hacer ésto?:

Tengo la tabla Ciudades con 2 campos: Pais y ciudad:

Busco el churro sql que genere este resultado

Pais Ciudad Contador_De_Ciudades_En_Pais
----------------------------------------------------------------------------------------------------
España Madrid 1
España Barcelona 2
España Valencia 3
Italia Roma 1
Italia Milan 2

Gracias,
Un saludo

Espero sirva:

-- IIslas
-- Creando un consecutivo, agrupado por una columna.
create table ciudades(
PAIS VARCHAR(20) not null,
CIUDAD VARCHAR(20) not null
)
GO

insert into ciudades(PAIS, CIUDAD)
values('MEXICO', 'MONTERREY')
insert into ciudades(PAIS, CIUDAD)
values('MEXICO', 'GUADALAJARA')
insert into ciudades(PAIS, CIUDAD)
values('MEXICO', 'DISTRITO FEDERAL')
insert into ciudades(PAIS, CIUDAD)
values('MEXICO', 'TIJUANA')
insert into ciudades(PAIS, CIUDAD)
values('ESPAÑA', 'VALENCIA')
insert into ciudades(PAIS, CIUDAD)
values('ESPAÑA', 'MADRID')
insert into ciudades(PAIS, CIUDAD)
values('ESPAÑA', 'BARCELONA')
insert into ciudades(PAIS, CIUDAD)
values('ITALIA', 'ROMA')
insert into ciudades(PAIS, CIUDAD)
values('ITALIA', 'MILAN')
GO

SELECT PAIS, (SELECT COUNT(*)
FROM ciudades AS I
WHERE I.PAIS=J.PAIS
AND I.CIUDAD>=J.CIUDAD) AS No
, CIUDAD
FROM ciudades AS J
ORDER BY PAIS, No
go

DROP TABLE ciudades
GO

Gracias

El problema es que trabajo con mysql, no con sql Server, y todavía no se hacer tablas temporales en mysql. Si consigo migrar tu código al standard sql 2003 (es el que usa mysql) , lo escribiré aqui por si a alguien le sirve.

Saludos para Méjico

Batilio

Entonces como sugerencia, indica en tu pregunta, el motor de base de datos que ocupas, ejemplo:

"Hola, estoy utilizando MySQL Ver. xxxxx, y tengo el siguiente problema........"

Ahora bien, en mi codigo, no existen TABLAS TEMPORALES, solo estoy haciendo uso de un SUB-QUERY