CODEWITHSUNDEEP
sqlsql-serverselectsql-order-bylimit
Aya Mohammad
Aya Mohammad

Reputation: 245

Retrieve the most used 10 records

I want to retrieve the most used 10 records in a field in my database. I used this query but it didn't work !

select Top 10 tag from articles order by count(tag) desc ;

This is the error I'm getting:

Column 'article.tags' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 99

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT TOP 10 tag, COUNT(tag) tagCount
FROM articles 
GROUP BY tag
ORDER BY tagCount DESC

Upvotes: 3

user240141
user240141

Reputation:

You can't identify with a simple select top statement. I would suggest you to involve one more table to keep track of id of most selected record something like this:

ArticleId Counter LastUsed
--------------------------
1           5     11/12/2013
3           11    10/12/2013    // and so on

Using this table join with your main table and pick the mostly used top 10 ids, based on their counter.

Select * From MainTable Where ArticleId in (
    Select Top(10) ArticleId
    From Table
    Order By Counter Desc)

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

Select TOP 10 tag From
(
Select tag,count(*) as total
From articles
Group by tag

) z
order by total Desc

Upvotes: 2

Related Questions