Limiting the count query in MySQL?

Question

I am trying to do a simple test where I'm pulling from a table the information of a specific part number as such:

SELECT *
FROM table_name
WHERE part_no IN ('abc123')

This returns 25 rows. Now I want to count the number that meet the "accepted" condition in a specific column but the result is limited to only the 10 most recent. My approach is to write it as follows:

Select Count(*)
FROM table_name
WHERE part_no IN ('abc123') AND lot IN ('accepted')
ORDER BY date DESC
LIMIT 10

I'm having a hard time to get the ORDER BY and LIMIT operations to work. I could use help just getting it to limit appropriately, and I can figure out the rest from there.

Edit: I understand that the operations are happening on the COUNT which only returns one row with a value; but I put the second clip to show where I am stuck in my thought process.

No point using IN() with only one value. Thats what = is for WHERE part_no = 'abc123' AND lot = 'accepted' — RiggsFolly
– RiggsFolly, Commented Jul 21, 2020 at 21:45
A SELECT count(*) .... will return only ONE ROW with the count on it, so the ORDER BY and LIMIT are a complete waste there — RiggsFolly
– RiggsFolly, Commented Jul 21, 2020 at 21:46
@RiggsFolly yes, I know they are being wasted but I am trying to figure out where LIMIT and ORDER BY can go to not do anything. I only proposed the clip above to show where I'm stuck in the thought process, and it doesn't seem as simple as fixing with parentheses. — Triscuitz
– Triscuitz, Commented Jul 21, 2020 at 21:54

Jan Schejbal · Accepted Answer · 2020-07-21 21:52:28Z

1

Your query SELECT Count(*) FROM ... will always return exactly one row.

It's not 100% clear what exactly you want to do, but if you want to know how many of the last 10 have been accepted, you could use a subquery - something like:

SELECT COUNT(*) FROM (
    SELECT lot
    FROM table_name
    WHERE part_no IN ('abc123')
    ORDER BY date DESC
    LIMIT 10
)
WHERE lot IN ('accepted')

The inner query will return the 10 most recent rows for part abc123, then the outer query will count the accepted ones.

There are also other solution (for example, you could have the inner query output a field that is 0 when the part is not accepted and 1 when the part is accepted, then take the sum). Depending on which exact dialect/database you are using, you may also have more elegant options.

answered Jul 21, 2020 at 21:52

Jan Schejbal

4,06323 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Triscuitz Over a year ago

Yep, this did it. You understood my intent, and thank you very much for the help. I was having a hard time running and understanding sub queries since in the past I got errors saying I can't use LIMIT in subqueries in my current version of MySQL. This additionally helped me understand in general how to use inner queries effectively, so thank you again.

Pius T.K · Accepted Answer · 2020-07-21 21:53:45Z

0

Select count returns ONE ROW therefore the ORDER BY and the LIMIT will not work on the results

answered Jul 21, 2020 at 21:53

Pius T.K

4358 silver badges9 bronze badges

Collectives™ on Stack Overflow

Limiting the count query in MySQL?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related