StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can't use an aggregate function (max) in a non-aggregated query. What you want is something like this:</p> <pre><code>SELECT * FROM TBLENTITYLOCATION TL INNER JOIN TBLENTITY TE ON TE.ENTITYID = TL.ENTITYID WHERE TE.ENTITYNAME = 'foobar' AND LOCATIONDATETIME = (select MAX(LOCATIONDATETIME) FROM TBLENTITYLOCATION TL INNER JOIN TBLENTITY TE ON TE.ENTITYID = TL.ENTITYID WHERE TE.ENTITYNAME = 'foobar') </code></pre> <p>The basic rule is that, if you're using aggregate functions (i.e. min, max, avg, etc.), then all of the columns in the select statement must be in an aggregate function or a part of the GROUP BY clause. Even if you had the GROUP BY (which wouldn't do what you need, in this case) your original query would still be invalid because you can't reference aggregate functions in the WHERE clause. To filter by an aggregate function, you need to use the HAVING clause, which is applied after the results are aggregated (as opposed to WHERE, which is evaluated before).</p> <hr> <p>Alternately, you could use ROWNUM and an ORDER BY clause to achieve the same result (essentially Oracle's version of TOP):</p> <pre><code>select * from (SELECT tl.*, te.*, rownum as rn FROM TBLENTITYLOCATION TL INNER JOIN TBLENTITY TE ON TE.ENTITYID = TL.ENTITYID WHERE TE.ENTITYNAME = 'foobar' ORDER BY LOCATIONDATETIME DESC) where rn = 1 </code></pre> <p>It may look like you could collapse this down into a single select, but this is an illusion. If you were to do that the ROWNUM criteria would be applied before the ORDER BY, so you would get a semi-random row.</p> <p>I believe the first version would be more efficient as it doesn't require that the results be sorted.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload