2

I have a question about getting results after limiting the rows.

I don't have much experience with MySQL, but I'm trying to get results from a database where I need to do the where clause after limiting the results.

SELECT
  COUNT(measurement) as 'measurement errors'
FROM measurelog
WHERE (measurement < (baseline - tolerance))
ORDER BY Id DESC
LIMIT 100

I only need the measurement values of the last 100 rows, so I expect to get about 5~10 results, but now the programm counts untill it has found 100 faulty measurements.

I've tried to switch the where clause to the bottom, but then I get an syntax error from the MySQL client.

Is there something I'm missing? Is there another way to do this?

  • I only need the measurement values of the last 100 rows You MUST use subquery for isolate this 100 records, and then perform counting in outer query. But your query do not contain GROUP BY clause at all - so you always will get only one output record, one count value (and always 100 if you'll isolate 100 records). – Akina Nov 6 '18 at 9:40
3

Thank you Akina for the tip, got it working using the following query

SELECT 
      COUNT(measurement) as 'Measurement Errors'
     FROM ( 
        SELECT *
        FROM measurelog
        ORDER BY Id DESC
        LIMIT 100
        ) sub
    WHERE (measurement < (baseline - tolerance))

which now returned 7 results

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.