CODEWITHSUNDEEP
mysqlsearchlimit
Michael
Michael

Reputation: 27

MySQL Search X amount of rows for specific terms

I have a database with a little over 300 rows.

For various reasons I only want to search 21 rows at a time for specific terms and at different starting index points.

I am querying for 'banned commercials' in a column called search_terms.

When I use the query below it searches all 300 rows 'banned commercials' instead of just 21 rows.

SELECT `rating_score`
FROM archived_videos
WHERE search_terms='banned commercials'
ORDER BY `rating_score` DESC
LIMIT 0,21

Upvotes: 1

Views: 81

Answers (3)

ccleve
ccleve

Reputation: 15809

I take it from your question that you want to search a block of 21 rows, not limit the result set to 21 rows. The LIMIT clause limits the results, not the set of records being searched. To limit records, you need to add something to your where clause. What this is depends on the contents of your table. If you had a sequence column that numbered the rows from 1 to 300, you could say "where sequence>= 1 and sequence < 21", and adjust the numbers as appropriate.

Another way to do it is to add a select clause: 

select iif (search_terms="banned", 1, 0) as flag
into temp_table
from archived_videos
limit 21

select * from temp_table where flag = 1

You might be able to combine these two selects into one using a subselect.

Upvotes: 0

Olaf Dietsche
Olaf Dietsche

Reputation: 74098

If you want to search the top 21 rated rows, you can do this with a subselect

SELECT t.`rating_score`
from (select *
      FROM archived_videos
      ORDER BY `rating_score` DESC
      LIMIT 0,21) t
WHERE t.search_terms='banned commercials'

Upvotes: 1

Arunkumar Srisailapathi
Arunkumar Srisailapathi

Reputation: 1169

Try using this 

SELECT rating_score FROM archived_videos WHERE search_terms='banned commercials' ORDER BY rating_score DESC LIMIT 21

Limiting it specifying 21 will limit the results to first 21

Upvotes: 1

Related Questions