Koban Koban - 1 year ago 47
SQL Question

Finding an ID with the maximun value of an attribute in a group with SQL

If we have a simple table (SQLite syntax):

CREATE TABLE Receptions (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
ID_Patients INTEGER NOT NULL,
ID_Doctors INTEGER NOT NULL,
StartDateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);


filled with some ids, what is the best SQL query for getting doctor with latest date for each patient? Is it possible to optimize the following example somehow to make it work faster?

SELECT ID_Patients pid, ID_Doctors did FROM Receptions INNER JOIN
(SELECT MAX(StartDateTime) maxDate, ID_Patients pid FROM Receptions GROUP BY pid) a
ON a.pid = pid AND a.maxDate = StartDateTime;


I wonder if anyone can explain how this query is executed and what data structures are created on the server side (assuming there are all required indices).

Answer Source

This might work faster with a correlated subquery:

SELECT r.*
FROM Receptions r
WHERE r.StartDateTime = (SELECT MAX(r2.StartDateTime)
                         FROM FROM Receptions r2
                         WHERE r2.pid = r.pid 
                        );

For performance, you want an index on Receptions(pid, StartDateTime).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download