Hello. Please tell me what is the difference between requests: this

select count(*) from `drivers` where `user_id` = 1 and (`way1` = 25 or `way2` = 25 or `way3` = 25) and `travel_date`>1442682000

here I counted out the last 10 lines in the database and from the 11th one I took travel_date , and by this

 select count(*) from `drivers` where `user_id` = 1 and (`way1` = 25 or `way2` = 25 or `way3` = 25) order by `travel_date` desc limit 10

You need to select the count for the last 10 drivers (dates (travel_date) go in ascending). The first query selects everything correctly, but the fact is that it does not suit me, because to calculate the condition ( travel_date > 1442682000), you need to be very confused. I wanted to remake the request without this condition, I got the second option. But he gives the wrong answer. The correct answer is 3, but it gives out 181. Tell me why so?

  • Because the second query first counts count (), and then returns 10 records, and there are only 1 of them after count () without grouping. - Visman
  • Is it possible to make count () count after selecting 10 records in one query? - Konstantin
  • I don’t understand what count () counts here, but you can use SELECT COUNT(*) FROM (SELECT * FROM travel_date LIMIT 10) AS tmp - Visman
  • figured out, thank you so much for your help. It turned out so SELECT COUNT(*) FROM (SELECT * FROM drivers` where user_id = 1 order by trawel_date desc LIMIT 10) as tmp where ( way1 = 25 or way2 = 25 or way3 = 25) ` - Konstantin

1 answer 1

what is the difference between requests

awesome. the second query, for example, will return a number less than or equal to ten (even if more than ten lines fall under the listed conditions, the limit 10 directive takes only ten of them, and the result of the query will be the number 10 ).