Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Checking if a keyword is in the top 1000

    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.
    Code:
    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.

  • #2
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    PHP Code:
    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.

  • #3
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Unfortunately that wouldn't work because lookup_count rarely even goes up to 100 so all records would be returned with that query.

  • #4
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    What field is used to determine what "number" the keyword is in the list? What exactly is lookup_count used for?

  • #5
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    The field lookup_count simply keeps track of the number times a specific keyword has been queried for.

  • #6
    New Coder
    Join Date
    Jul 2006
    Location
    Cincinnati, OH
    Posts
    85
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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!

  • #7
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •