View Full Version : Checking if a keyword is in the top 1000

08-09-2006, 04:25 PM
How can I structure a query so that I only get back a result if the keyword I am searching for is in the top 1,000 of the results?

I am currently using the following query and then running through the array using PHP to check if the keyword is in the top 1,000. However, I would like to eliminate the PHP step to save processing time and CPU usage.

SELECT keyword FROM lookup_table ORDER BY lookup_count DESC LIMIT 0, 1000

After getting back the result above I would use a loop to get all 1,000 keywords. If I could just check whether the query succeeded or failed based on the keyword that would be much better.

08-09-2006, 04:59 PM
SELECT keyword FROM lookup_table WHERE keyword = '$keyword' AND lookup_count <= 1000

Would this do what you are wanting to do? That's all I could think of.

08-09-2006, 05:14 PM
Unfortunately that wouldn't work because lookup_count rarely even goes up to 100 so all records would be returned with that query.

08-09-2006, 05:16 PM
What field is used to determine what "number" the keyword is in the list? What exactly is lookup_count used for?

08-09-2006, 05:25 PM
The field lookup_count simply keeps track of the number times a specific keyword has been queried for.

08-09-2006, 05:44 PM
Ok, that makes sense. Unfortunately it also derails my train of thought. It doesn't seem like you would be able to work this without going through the array. Hopefully someone with more experience comes along with something that can help you out! Good luck!:thumbsup:

08-09-2006, 06:38 PM
select 1 from lookup_table where keyword='something' and keyword IN (SELECT keyword FROM lookup_table ORDER BY lookup_count DESC LIMIT 0, 1000)

This will return null if 'something' is not in the top 1000, and it will return 1 if it is in the top 1000. You can substitute a field name for the "1" if there's any data you want returned.

One potential issue... if you have a tie for 1000th place it might only list a subset of the items in the tie.