CODEWITHSUNDEEP
sqlsql-servercountselect-query
user3548681
user3548681

Reputation:

sql select 5 higest values

I'm trying to select the 5 rows with the highest count value

This is my query:

string sql = "SELECT   top 5  count FROM  Likes  ORDER BY COUNT(*) DESC";

It's just throwing an error code that

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

It's for a project I've got to present tomorrow... enter image description here

Upvotes: 2

Views: 171

Answers (3)

Ash Burlaczenko
Ash Burlaczenko

Reputation: 25465

count is a reserved word which is why you should stay clear of using them for column names. If you don't want to rename the column you can escape it, different dbms may effect who you do this. In ssms you would use square brackets.

string sql = "SELECT   top 5  [count] FROM  Likes  ORDER BY COUNT(*) DESC";

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18747

You should not use COUNT(*) here for order by.

SELECT   top 5  [count] FROM  Likes  ORDER BY [Count] DESC

Upvotes: 2

Dan
Dan

Reputation: 10680

On SQL Server, simply do this:

 SELECT TOP 5 * FROM Likes ORDER BY [Count] DESC

This assumes that your Likes-table already contains a column named [Count] meaning that you don't need to count the records yourself (which is what COUNT(*) does).

Upvotes: 5

Related Questions