SQLite Order By foreign table field

user_14430081 0
Viewed 98

I have two tables like following: (They have much more fields in reality)

Records (
    recordId INTEGER PRIMARY KEY,
    dateTime TEXT
);

Alarms (
    alarmId INTEGER PRIMARY KEY,
    recordId INTEGER,
    FOREIGN KEY (recordId) REFERENCES Records(recordId)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

I want to get all Alarms sorted by dateTime of Record they are referring. I think it should be something like this:

SELECT * FROM Alarms ORDER BY Records.dateTime DESC;

But I couldn't find any relatable example. Thank you for your time.

sql-order-by foreign-keys sqlite
1 Answers

What you need is a join of the 2 tables:

SELECT a.* 
FROM Alarms a INNER JOIN Records r
ON r.recordId = a.recordId 
ORDER BY r.dateTime DESC;

Or a correlated subquery in the ORDER BY clause:

SELECT a.* 
FROM Alarms a 
ORDER BY (SELECT r.dateTime FROM Records r WHERE r.recordId = a.recordId) DESC;
user_104988280
Related