1

In a sqlite database i have a "Users" table as follows

Users:

| user_id  | user                       
| 1        | John               
| 2        | Peter

a "Posts" table as follows:

| post_id | user_id     | text
| 10      | 1           | blah blah blah
| 11      | 1           | blah blah blah
| 12      | 2           | blah blah blah

I'm using this code to join two tables:

SELECT * FROM Users JOIN Posts USING ( user_id )

Now i have a list of all posts by different users as follows:

| user_id  | user         | post_id   | text           
| 1        | John         | 10        | blah blah blah
| 1        | John         | 11        | blah blah blah
| 2        | Peter        | 12        | blah blah blah

Assuming that the post with bigger post_id is posted more recently.
Now i want a list of most recent posts by each users (row #2 and #3 in this case).
How can i do this?
I'm thinking of deleting all rows (for each users) , except the rows which has biggest post_id (most recent), is this a good solution? how should i query it?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Behnam Maboudi
  • 655
  • 5
  • 21
  • Possible duplicate of [SQL: Filter rows with max value](http://stackoverflow.com/questions/24708109/sql-filter-rows-with-max-value) – Shadow Apr 03 '17 at 15:51

3 Answers3

3

You can use an inner query that gets you the latest post for each user, then that table will act as a filter when joined with the other two.

select  *
from    users u
join    (
            select  user_id, max(post_id) post_id
            from    Posts
            group by user_id
        ) r
on      u.user_id = r.user_id
join    posts p
on      r.post_id = p.post_id and
        r.user_id = p.user_id
Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
  • This gives me 3 duplicate columns! but thanks , i changed it a bit and it worked fine. i removed 3 last lines and changed "on u.user_id ..." to "using (id)" – Behnam Maboudi Apr 03 '17 at 16:26
2

In SQLite (but not in any other DB), when using GROUP BY, you can use max() or min() to select which row to return in a group:

SELECT user_id,
       user,
       max(post_id) AS post_id,
       text
FROM Users
JOIN Posts USING ( user_id )
GROUP BY user_id;
CL.
  • 173,858
  • 17
  • 217
  • 259
0

you have to change the query a little bit. you have to write the query as follows:

SELECT * FROM Users Join Posts Using(user_id) order by post_id desc;
Shadow
  • 33,525
  • 10
  • 51
  • 64
khandoker jobayer
  • 11
  • 5