Need help in order by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help in order by

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2008-06-04 : 16:04:45

Im trying to select the 5 top documents in the research_downloads table and order them by greatest number desc. The query below is returning the recordset ordered by content_title. Should I be using a count() in the select instead of the TOP and use the count() in the Order By?

SELECT Distinct Top 5 [content].content_id, [content].content_title FROM research_downloads INNER JOIN [content] ON = [content].content_id
WHERE [content].folder_id=30 AND CHARINDEX('<PrimarySubjectArea>Content</PrimarySubjectArea>', content_html) > 1
Group By [content].content_id, [content].content_title, research_downloads.insert_date

ORDER BY ??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 16:07:31

What do you mean by "order them by greatest number desc"? What is greatest number?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

ljp099
Yak Posting Veteran

79 Posts

Posted - 2008-06-04 : 17:08:49

content_id | date
100
100
100
100
100
99
99
99
99
102
103
104
104
101
101
101

With these column values, the results would be ordered:

DocId
100 (5 total)
99 (4 total)
101 (3 total)
104 (2 total)
102 (1 total)
103 (1 total)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 00:23:19

[code]SELECT content_id
FROM Table
GROUP BY content_id
ORDER BY COUNT(*) DESC[/code]

cruxmagi
Starting Member

38 Posts

Posted - 2008-06-05 : 02:23:42

SELECT content_id,COUNT(*) as 'Total'
FROM Table
GROUP BY content_id
ORDER BY COUNT(*) DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources