SQL Why does LIMIT 1 display more than one row?

Question

So I'm trying to display the titles where the number of actresses are 91, and from what I understood, LIMIT 1 should limit the number of ROWS to 1. But here, I'm actually getting 2 titles back that both have 91 actresses playing in it. Why does LIMIT 1 not limit the number of rows to 1 here?

Thanks in advance!

SELECT title, COUNT(*) c
FROM title_100k AS T
JOIN cast_info_100k AS CI ON CI.movie_id = T.id
JOIN role_type AS R ON R.id = CI.role_id
WHERE R.id = 2
GROUP BY(t.id))
SELECT title FROM tempTable WHERE c IN
(SELECT c from tempTable 
EXCEPT SELECT c from tempTable WHERE c IN (SELECT c from tempTable WHERE c > 91) ORDER By c desc LIMIT 1);```

Your `LIMIT` is inside a subquery you're using for a semi-join. The limit is not applied to the outer query, so the outer query is free to return any number of rows that match the subquery. — Bill Karwin, Mar 19 '21 at 15:13
Also the query you posted is incomplete. I guess `tempTable` is a CTE, but you've chopped off the first line that starts the CTE. — Bill Karwin, Mar 19 '21 at 15:14
Which dbms are you using? `LIMIT` is a product specific functionality. — jarlh, Mar 19 '21 at 15:22

score 1 · Accepted Answer · answered Mar 19 '21 at 15:16

It might because your LIMIT 1 is inside

(SELECT c
       from tempTable EXCEPT SELECT c
       from tempTable
       WHERE c IN (SELECT c from tempTable WHERE c > 91)
       ORDER By c desc
       LIMIT 1)

instead of outside

(SELECT c
       from tempTable EXCEPT SELECT c
       from tempTable
       WHERE c IN (SELECT c from tempTable WHERE c > 91)
       ORDER By c desc
       ) LIMIT 1

But you should check this answer How do I limit the number of rows returned by an Oracle query after ordering? because with newer version of Oracle, there are new options to limit the number of results.

LIMIT doesn't work with Oracle to begin with. – Mar 19 '21 at 15:21 — , Mar 19 '21 at 15:21

SQL Why does LIMIT 1 display more than one row?

1 Answers1