CODEWITHSUNDEEP
sqlgreatest-n-per-group
Koban
Koban

Reputation: 523

Finding an ID with the maximum 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).

Upvotes: 1

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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

Upvotes: 1

Related Questions