SQLite: Loop over SELECT statement, replacing the WHERE clause

Question

I have following two tables:

CREATE TABLE messages (
  id integer UNIQUE NOT NULL,
  message text,
  recipient integer NOT NULL,
  sender integer NOT NULL,
  sent_at text NOT NULL,
  FOREIGN KEY (recipient) REFERENCES users (id),
  FOREIGN KEY (sender) REFERENCES users (id)
);
CREATE TABLE users (
  id integer UNIQUE NOT NULL,
  username text NOT NULL,
);

I need a very specific query, that looks like the following:

SELECT *
FROM messages
WHERE sender = 123 OR recipient = 123
ORDER BY id desc
LIMIT 1

I need to kind of iterate over the messages table, using every user, and putting him in the WHERE statement.

-- TABLE 'users':
-- 123 = id of user1
-- 456 = id of user2
-- 789 = id of user3

Is it possible to iterate in SQLite?

Goal is, to get the newest "conversation" for every user in the users table. For every user, the newest message involving him should be displayed, no matter if that newest message was sent or recieved by him.

Well, I know it exists. I just couldn't imagine that it would be of help, here. However, I take your question for a hint and will try to use it somehow. :) — msal, May 05 '14 at 23:53
possible dup of [How to outer-join two tables (main and many-to-one sub-table) to get only ONE item from second table?](http://stackoverflow.com/questions/667459/), [Fetch the row which has the Max value for a column](http://stackoverflow.com/q/121387/90527) — outis, Dec 14 '14 at 01:53
"Iteration" is a programing concept. To deal with RDBs, one should think relationally: join relations to create new relations, filter & group to get relevant rows. — outis, Dec 14 '14 at 01:56

score 2 · Accepted Answer · answered May 06 '14 at 08:14

2

You could use a correlated subquery to get that value for each user ID:

SELECT id,
       username,
       (SELECT MAX(id)
        FROM messages
        WHERE sender    = users.id
           OR recipient = users.id
       ) AS last_message_id
FROM users

This is also possible with GROUP BY. First join the two table together, then create a group for each user:

SELECT users.id,
       MAX(messages.id)
FROM users
JOIN messages ON users.id = messages.sender OR
                 users.id = messages.recipient
GROUP BY users.id

answered May 06 '14 at 08:14

CL.

173,858
17
217
259

These solutions are not just perfect, but especially the first one is pure elegance. I was thinking way too complicated. Kudos! – msal May 06 '14 at 08:51
This assumes message IDs are strictly increasing over time (which is a pretty reasonable one to make, given the schema details provided). – Scott Hunter May 06 '14 at 14:07

score -1 · Answer 2 · answered Aug 01 '20 at 12:12

-1

SELECT year , COUNT(*) AS Count 
FROM Movie
WHERE Movie.MID NOT IN
       (SELECT DISTINCT m.MID 
         FROM Movie m 
         JOIN M_Cast m_c ON m.MID = m_c.MID 
         JOIN Person p_1 ON m_c.PID = p_1.PID  
         AND p_1.Gender='Male')
AND Movie.MID IN
       (SELECT DISTINCT m.MID 
         FROM Movie m 
         JOIN M_Cast m_c ON m.MID = m_c.MID 
         JOIN Person p_1 ON m_c.PID = p_1.PID 
         AND p_1.Gender='Female')
GROUP BY year;

answered Aug 01 '20 at 12:12

Shaurya Cairae

1

1

While this code snippet may solve the question, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Maximouse Aug 01 '20 at 15:57

SQLite: Loop over SELECT statement, replacing the WHERE clause

2 Answers2