Query without repeated data SQL Server

Question

Query without repeated data SQL Server

Navigation

#1 by (2 votes)

2

You will see I need to send to ask for an amount n of questions, where that amount is given by a parameter. The query is already functional and sends them to the parameter randomly.

What happens now is that it shows questions that are repeated, and should show unique questions, so I was seeing some things of SELECT DISTINCT but I'm not sure how to implement it since there is already a SELECT TOP sentence.

Here is the query, it's done in SQL Server 2012 :

ALTER PROCEDURE [dbo].[spListarPreguntasPorTema]
(@prmLimite int,
@prmCodTema int
)
AS
    BEGIN
        SELECT TOP (@prmLimite) p.cod_pregunta, p.pregunta
        FROM dbo.pregunta AS p
        INNER JOIN dbo.tema AS t
        ON p.cod_tema = @prmCodTema AND @prmLimite = t.no_preguntas
        ORDER BY NEWID()
    END

sql sql-server select distinct

asked by Oscar Anibal 03.10.2018 в 23:06

source

1 answer

Receive different values of a PHP variable in HTML text Browse Objects Array with Jquery

score 2 · Accepted Answer

You mark the error because all those fields that you are going to group must be specified in GROUP BY , in this case you would need to add the field cod_pregunta . The problem you would have is that if the field cod_pregunta is a single value or unique , it will keep repeating the results. Your query would be more or less like this:

  SELECT TOP (@prmLimite) p.cod_pregunta, p.pregunta
    FROM dbo.pregunta AS p
    INNER JOIN dbo.tema AS t
    ON p.cod_tema = @prmCodTema AND @prmLimite = t.no_preguntas
    GROUP BY p.cod_pregunta, p.pregunta
    ORDER BY NEWID()

Although it is not clear to me why you do a JOIN with the parameter @prmCodTema , I think it would be more efficient to do a WHERE p.cod_tema = @prmCodTema . On the other hand the @prmLimite = t.no_preguntas in your JOIN I do not understand very well so you do it. Maybe if you put the structure of your tables we could help you better. Greetings.