CODEWITHSUNDEEP
sqlsql-servert-sqldistinct
amiry jd
amiry jd

Reputation: 27585

How to use SELECT DISTINCT and SELECT TOP n in T-SQL

I have this query:

ALTER PROCEDURE [dbo].[MySP]
    @Q Nvarchar(512)
AS BEGIN

SET @Q = REPLACE(@Q, N'%', N'');
SET @Q = N'"' + @Q + N'*' + N'"';

SELECT TOP 8 
       [KeywordId] as ID,
       [Keyword] as lable,
       [Keyword] as value
  FROM [dbo].[News_Keywords]
  WHERE CONTAINS ([Keyword], @Q)
  ORDER BY SortOrder, len([Keyword])

END

What I'm asking for, is how can I apply DISTINCT functionality (on column [Keyword]) to this statement? It's completely confusing me! Thanks in advance.

Upvotes: 0

Views: 2755

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use group by instead of distinct. However, you have to decide where to put SortOrder. Here is one method:

SELECT TOP 8 [KeywordId] as ID, [Keyword] as label, [Keyword] as value
FROM [dbo].[News_Keywords]
WHERE CONTAINS ([Keyword], @Q)
GROUP BY KeywordId, KeyWord, SortOrder
ORDER BY SortOrder, len([Keyword]);

Here is another:

SELECT TOP 8 [KeywordId] as ID, [Keyword] as label, [Keyword] as value
FROM [dbo].[News_Keywords]
WHERE CONTAINS ([Keyword], @Q)
GROUP BY KeywordId, KeyWord
ORDER BY MAX(SortOrder), len([Keyword]);

Upvotes: 2

Related Questions