Fix sql / hql query (aggregate in where section)

Question

I want to make a request as shown below. The request is incorrect, but describes my intentions.

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

Update: Good. The request does not describe intentions well. My bad.

I want to select the last entry for each person.

Answer 1

I like Mickey's answer from Quassnoi (and supported Mickey), but if your needs are similar to mine, you should keep in mind some limitations. First and foremost, it only works if you are looking for the most recent entry or the most recent entry for a single name. If you need the last entry for each person in the set (one entry per person, but the last entry for each), then the above solutions do not justify themselves. Secondly, and less importantly, if you work with large data sets, it can be a little slow in the long run. So what is work?

What I am doing is adding a bit to the table labeled "newest". Then, when I store a record (which runs 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); 

In addition, there is an index in Name and Newest to make the selection very quick.

Then select "Select":

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

The selection for the group will look something like this:

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

If entries cannot be entered in date order, your logic is slightly 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 remains unchanged.

Answer 2

Try the following:

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

You can also write it using an internal join:

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

What 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 3

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 that:

 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 on your blog:

• SQL Server: Selecting records that have a group maximum

for the advantages and disadvantages of both solutions.

Answer 4

I tried Milky's advice, but all three methods of constructing the subquery led to HQL parser errors.

What works is a slight change to the first method (additional parentheses added).

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

PS: It's just to point out the obvious for HQL beginners and the like. Thought it would help.