0

This below query works as expected:

SELECT 
    reports.* 
FROM
    reports 
    INNER JOIN 
        units 
        ON units.id = reports.unit_id 
WHERE 
    reports.unit_id IN (1111, 1112, 1113) 
    AND 
    (
        reports.id = 
        (
            SELECT reports.id 
            FROM reports
            WHERE reports.unit_id = units.id 
            ORDER BY time desc
            LIMIT 1
        )
    )

It returns the last 3 reports associated with the given units by time. How come that SELECT subquery returns 3 ids rather than just 1 even though we specifiy LIMIT 1? This is what I wanted. I'm just curious how it works.

billinkc
  • 15,893
  • 4
  • 51
  • 89
JohnMerlino
  • 1,739
  • 5
  • 18
  • 21
  • The LIMIT is only limiting the innermost query. The outer one is free to return lots of rows based on the single row you're feeding it – Philᵀᴹ Mar 06 '14 at 00:03
  • @Phil but how does the inner query know about the out filter of 3 unit ids? – JohnMerlino Mar 06 '14 at 00:19
  • 1
    It doesn't. It's a correlated subquery which means it runs as many times as the rows that are generated by the FROM ... WHERE clauses (which are 3 here.) – ypercubeᵀᴹ Mar 06 '14 at 00:20
  • Correction: The rows generated by the FROM clause, even after restricting with the first condition in the WHERE may be more than 3. But the distinct unit.id values in those rows are 3 and these values are used inside the correlated subquery. Whether the DBMS runs the subquery 3 or a million times depends on the implemenation (read optimizer.) – ypercubeᵀᴹ Mar 06 '14 at 00:26

0 Answers0