Hi, I have a table which has a col which is basically search terms. I want to select all and then show the top 10 search terms. Can this be done in a query or will I need to bring the whole recordset back and then do some server side sorting? Thanks
Something like select search_term, count(*) as termcount from tablename group by search_term order by count(*) desc This will show all search terms in the table. You can add TOP or LIMIT or whatever to only get the top 10 (depends on your type of SQL).
If the table is huge make sure you are at least indexing the table to speed up results. If the table is really really large you might want to consider caching the answer to memory, or creating a table specifically to return the top 10 searches based and a trigger that updates this table in the background any time a new search term is entered (or entered for the Xth time), or create a job that updates that table X times per day - depends upon how real-time and critical this infromation has to be. Running a process, say, every hour will make a huge difference to overall performance, and if the data is a max of 1 hour out-of-date, who will really care? That way when you are displaying the top 10 searches you are retrieving the info from memory or a very small table so speed is kept at a maximum. When your original table is small you may not notice any performance difference, but when it is huge, and it has to search through this every time someone access the search page, your site will slow to a crawl. You'll have to (eventually) find a solution so why not start off and create the solution now?