Help with this SQL query...

Question

The follow query is returning an error at column 143: "ORA-00934: group function is not allowed here"

SELECT * FROM 
TBLENTITYLOCATION TL INNER JOIN TBLENTITY TE ON TE.ENTITYID = TL.ENTITYID 
WHERE TE.ENTITYNAME = 'foobar' 
AND LOCATIONDATETIME = MAX(LOCATIONDATETIME)

It works fine if I take out the last line - AND LOCATIONDATETIME = MAX(LOCATIONDATETIME), but then it returns every instance of 'foobar' instead of just the latest. Can someone help me?

Answer 1

You could try:

SELECT Top(1) * FROM  
TBLENTITYLOCATION TL INNER JOIN TBLENTITY TE ON TE.ENTITYID = TL.ENTITYID  
WHERE TE.ENTITYNAME = 'foobar'  
ORDER BY LOCATIONDATETIME DESC

Answer 2

Take out MAX(LOCATIONDATETIME) from the where clause and put it in your select clause:

SELECT MAX(LOCATIONDATETIME) FROM TBLENTITYLOCATION TL INNER JOIN TBLENTITY TE ON TE.ENTITYID = TL.ENTITYID WHERE TE.ENTITYNAME = 'foobar'

Also add other fields to the select clause as selecting * is bad practise

Answer 3

Alternatively you can use a subquery in the WHERE clause like so:

SELECT * FROM 
TBLENTITYLOCATION TL INNER JOIN TBLENTITY TE ON TE.ENTITYID = TL.ENTITYID 
WHERE TE.ENTITYNAME = 'foobar' 
AND LOCATIONDATETIME = (SELECT MAX(LOCATIONDATETIME) FROM TBLENTITYLOCATION)

Answer 4

Use the MAX in the Select statement

SELECT *, MAX(LOCATIONDATETIME) FROM TBLENTITYLOCATION TL INNER JOIN TBLENTITY TE ON TE.ENTITYID = TL.ENTITYID WHERE TE.ENTITYNAME = 'foobar'

Answer 5

You could sort the Query descending, then just use the first row.

Answer 6

You can't use an aggregate function (max) in a non-aggregated query. What you want is something like this:

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')

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).

---

Alternately, you could use ROWNUM and an ORDER BY clause to achieve the same result (essentially Oracle's version of TOP):

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

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.

I believe the first version would be more efficient as it doesn't require that the results be sorted.