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.
| Author |
Topic |
|
Starcraft
Starting Member
3 Posts |
 Posted - 2004-09-13 : 11:25:11
|
| There's a forum I built for one of my employer's websites that has over 1.5 million posts on it. To keep the search on it from killing the server (because it's an old server and averages around 400 forum users at a time), I've limited it to displaying only the top 500 results, and if there are more than 500, it informs the user to please try a more specific search. When using the code below (which is an excerpt of the code from the search sproc that this relates to), it displays the first 500 results, starting with the newest of the 500 oldest. I need this thing to work so that it displays the 500 newest records, however the ORDER BY seems to only fire after the initial records are chosen. Any way around this? If I reverse the primary key index order on the posts table, would it solve my problem?SELECT P.ID AS PostID, T.ID As TopicIDFROM Posts PINNER JOIN Topics T ON T.ID = P.TopicINNER JOIN CONTAINSTABLE(Posts, Message, ' FORMSOF INFLECTIONAL,"apple") ', 501) AS P2 ON P.ID = P2.[KEY]ORDER BY P.ID DESC |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-13 : 11:32:15
|
try this:SELECT distinct P.ID AS PostID, T.ID As TopicIDFROM Topics T INNER JOIN (select top 100 percent * from Posts order by Id desc) P ON T.ID = P.TopicINNER JOIN CONTAINSTABLE(Posts, Message, ' FORMSOF INFLECTIONAL,"apple") ', 501) AS P2 ON P.ID = P2.[KEY]Go with the flow & have fun! Else fight the flow  |
 |
|
|
Starcraft
Starting Member
3 Posts |
Posted - 2004-09-13 : 11:55:03
|
| Thanks for the effort, but it still just grabs the first 500 :( |
|
|
|
|
|
|
|
|
CONTAINSTABLE order by