Salve,
ti consiglio l'utilizzo della windowing function RANK() [
https://docs.microsoft.com/it-it/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver15], dove puoi "ordinare" in base al count raggruppato, ottenendo quindi l'ordinalita' nel subset di ricerca, dal quale escludere tutte le valorizzazioni' superiori a 1...
per capirci, [ho tolto la parola chiave CREATE] per non penalizzare e far bloccare la riposta dai filtri del forum:
TABLE [dbo].[Citta] (
[Codice] varchar(9) NOT NULL PRIMARY KEY,
[Nome] varchar(10) NOT NULL
);
TABLE [dbo].[Persone] (
[Id] int NOT NULL PRIMARY KEY,
[Nome] varchar(15) NOT NULL,
[CodiceCitta] varchar(9) NOT NULL
CONSTRAINT [fk_Persone$has$Citta] FOREIGN KEY
REFERENCES [dbo].[Citta] ([Codice])
);
GO
INSERT INTO [dbo].[Citta]
VALUES ( '403015146', 'MILANO' ), ( '408099013', 'RICCIONE' ), ( '412058091', 'ROMA' );
INSERT INTO [dbo].[Persone]
VALUES ( 1, 'SuperManPC', '403015146' )
, ( 2, 'Oregon', '412058091' )
, ( 3, 'Andrea', '408099013' )
, ( 4, 'Toki', '412058091' )
, ( 5, 'Migliorabile', '403015146' )
, ( 6, 'Lorenzo', '403015146' )
, ( 7, 'AndBin', '412058091' );
GO
PRINT 'proiezioni raggruppata';
SELECT c.[Codice], c.[Nome], COUNT(*) AS [c]
FROM [dbo].[Citta] c
JOIN [dbo].[Persone] p ON p.[CodiceCitta] = c.[Codice]
GROUP BY c.[Codice], c.[Nome]
ORDER BY COUNT(*) DESC;
PRINT 'utilizzo della windowing function RANK() per ottenere l''ordine nel set in base al count';
PRINT ' qui useremo appunto RANK() e non ROW_NUMBER() in quanto e'' ovviamente possibile che esistano';
PRINT ' svariate entita'' con lo stesso "valore", sia esso il massimo, il minimo o nel mezzo,';
PRINT ' poi filtreremo SOLO la/e riga/eh con il RANK = 1 per escludere le righe non interessanti;';
WITH cteCount AS (
SELECT c.[Codice], COUNT(*) AS [c]
FROM [dbo].[Citta] c
JOIN [dbo].[Persone] p ON p.[CodiceCitta] = c.[Codice]
GROUP BY c.[Codice]
),
cteOrdered AS (
SELECT c.[Codice], c.[c], RANK() OVER (ORDER BY c.[c] DESC) AS [rnk]
FROM cteCount c
)
SELECT ci.[Nome], c.[c] AS [Ccount]
FROM cteOrdered c
JOIN [dbo].Citta ci ON ci.[Codice] = c.[Codice]
WHERE c.[rnk] = 1;
come indicato, con RANK() possiamo ottenere l'ordinale in base al suo rango nel set, quindi ci e' facile escludere le righe che NON abbiano un rank = 1, quindi ottenendo solo Roma e Milano...
salutoni romagnoli (qui esclusi per un rank fuori dalla ricerca)
--
Andrea