Get a descending list of items using two tables

Question

CREATE TABLE [dbo].[HistoricoSeries] (
    [IDHistoricoSeries] INT IDENTITY (1, 1) NOT NULL,
    [IDUtilizador]      INT NOT NULL,
    [codepisodio]       INT NOT NULL,
    CONSTRAINT [PK_historicoseries] PRIMARY KEY CLUSTERED ([IDHistoricoSeries] ASC),
    CONSTRAINT [FK_96] FOREIGN KEY ([IDUtilizador]) REFERENCES [dbo].[Utilizadores] ([IDUtilizador]),
    CONSTRAINT [FK_hse] FOREIGN KEY ([codepisodio]) REFERENCES [dbo].[EPISODIOS] ([codepisodio])
);

CREATE TABLE [dbo].[EPISODIOS] (
    [idepisodio]     INT          IDENTITY (1, 1) NOT NULL,
    [codepisodio]    INT          NOT NULL,
    [codserie]       INT          NOT NULL,
    [codtemporada]   INT          NOT NULL,
    [numeroepisodio] INT          NOT NULL,
    [tituloepisodio] VARCHAR (53) NOT NULL,
    [duracaominutos] INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([codepisodio] ASC)
);

These are my table definitions.

string maisVistoEpisodio = "SELECT * FROM EPISODIOS WHERE EPISODIOS.codepisodio IN (SELECT codepisodio, count(codepisodio) AS mais_vistos FROM HISTORICOSERIES GROUP BY codepisodio ORDER BY COUNT (codepisodio) DESC)";

This is my SQL Server query that i've been from quite sometime hacking away with no results.

My end goal was to have a listing of the most watched episodes from the table EPISODIOS, but the error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

arose itself and I don't know how to fix.

Can anyone shed a bit of light? Thanks.

Answer 1

Your code looks like SQL Server. If so, you can use TOP WITH TIES in the subquery:

SELECT E.*
FROM EPISODIOS E
WHERE E.codepisodio IN (SELECT TOP (1) WITH TIES HS.codepisodio
                        FROM HISTORICOSERIES HS
                        GROUP BY HS.codepisodio
                        ORDER BY COUNT(*) DESC
                       )