Correct sql/hql query (aggregate in where clause)

Question

I want to do query as below. Query is wrong but describes my intentions.

SELECT name, dateTime, data
FROM Record
WHERE dateTime = MAX(dateTime)

Update: Ok. The query describes intentions not quite good. My bad.

I want to select latest record for each person.

Answer 1

Try This:

SELECT name, dateTime, data
FROM Record 
WHERE dateTime = SELECT MAX(dateTime) FROM Record

You could also write it using an inner join:

SELECT R.name, R.dateTime, R.data
FROM Record R
  INNER JOIN (SELECT MAX(dateTime) FROM Record) RMax ON R.dateTime = RMax.dateTime

Which is the same but written from a different perspective

SELECT R.name, R.dateTime, R.data
FROM Record R,
     (SELECT MAX(dateTime) FROM Record) RMax
WHERE R.dateTime = RMax.dateTime

Answer 2

In MySQL and PostgreSQL:

SELECT  name, dateTime, data
FROM    Record
ORDER BY
        dateTime DESC
LIMIT 1

In SQL Server:

SELECT  TOP 1 name, dateTime, data
FROM    Record
ORDER BY
        dateTime DESC

In Oracle

SELECT  *
FROM    (
        SELECT  name, dateTime, data
        FROM    Record
        ORDER BY
                dateTime DESC
        )
WHERE   rownum = 1

Update:

To select one person for each record, in SQL Server, use this:

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY person ORDER BY dateTime DESC)
        FROM    Record
        )
SELECT  *
FROM    q
WHERE   rn = 1

or this:

SELECT  ro.*
FROM    (
        SELECT  DISTINCT person
        FROM    Record
        ) d
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    Record r
        WHERE   r.person = d.person
        ORDER BY
                dateTime DESC
        ) ro

See this article in my blog:

• SQL Server: Selecting records holding group-wise maximum

for benefits and drawbacks of both solutions.

Answer 3

I like Miky's answer and the from Quassnoi (and upvoted Miky's) but, if your needs are similar to mine, you should keep in mind some limitations. First and most importantly, it only works if you are looking for the latest record overall or the latest record for a single name. If you want the latest record for each person in a set (one record per person but the latest record for each) then the above solutions fall short. Second, and less importantly, if you'll be working with large datasets, might prove a bit slow over the long run. So, what is the work-around?

What I do is to add a bit field to the table marked "newest." Then, when I store a record (which is done in a stored procedure in SQL Server) I follow this pattern:

Update Table Set Newest=0 Where Name=@Name
Insert into Table (Name, dateTimeVal, Data, Newest) Values (@Name, GetDate(), @Data, 1);

Also, there is an index on Name and Newest to make Selects very fast.

Then the Select is just:

Select dateTimeVal, Data From Table Where (Name=@Name) and (Newest=1);

A select for a group will be something like:

Select Name, dateTimeVal, Data from Table Where (Newest=1);  -- Gets multiple records

If the records may not be entered in date order, then your logic is a little bit different:

Update Table Set Newest=0 Where Name=@Name
Insert into Table (Name, dateTimeVal, Data, Newest) Values (@Name, GetDate(), @Data, 0); -- NOTE ZERO
Update Table Set Newest=1 Where dateTimeVal=(Select Max(dateTimeVal) From Table Where Name=@Name);

The rest stays the same.

Answer 4

I tried Milky's advice but all three ways of constructing subquery resulted in HQL parser errors.

What does work though, is a slight change to the first method (added extra parentheses).

SELECT name, dateTime, data
FROM Record 
WHERE dateTime = (SELECT MAX(dateTime) FROM Record)

PS: This is just for pointing out the obvious to HQL newbies and the like. Thought it would help.