How to get latest records

Question

I have a table looks like this:

+--------+-------------+----------+---------+
| PK(id) | FK(user_id) | ctime    | comment |
+--------+-------------+----------+---------+
| 1      | 1           | 20170101 | "Haha"  |
+--------+-------------+----------+---------+
| 2      | 2           | 20170102 | "Nope"  |
+--------+-------------+----------+---------+
| 3      | 2           | 20170104 | "Geez"  |
+--------+-------------+----------+---------+
| 4      | 1           | 20170110 | "Gone"  |
+--------+-------------+----------+---------+

I expect to retrieve latest records per FK(user_id) as followed:

+--------+-------------+----------+---------+
| PK(id) | FK(user_id) | ctime    | comment |
+--------+-------------+----------+---------+
| 3      | 2           | 20170104 | "Geez"  |
+--------+-------------+----------+---------+
| 4      | 1           | 20170110 | "Gone"  |
+--------+-------------+----------+---------+

So I tried SELECT DISTINCT T.* FROM my_table AS T and SELECT DISTINCT T.user_id FROM my_table AS T; however, they won't work!

I tried GROUP BY statement, as is followed,

SELECT T.* FROM my_table AS T GROUP BY `user_id` DESC

It does work perfectly as I expect. So I started to figure out how to translate SQL into Django!

First, I tried to use RawSQL:

from django.db.models.expressions import RawSQL

def _select_latest_rows(model_class, target_column_name, query_set=None):
    query_set = query_set or model_class.objects
    table_name = model_class._meta.db_table
    raw_sql = '''
    SELECT * FROM %s GROUP BY %s
    '''
    return query_set.annotate(val=RawSQL(raw_sql, (table_name, target_column_name,)))

Django pops the following error whenever I invoke this function.

django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax
blah blah blah...

Then I inspected my query string:

print(_select_latest_rows(model_class, target_column_name, query_set).query)

And it returns:

SELECT T.`id`, T.`user_id`, T.`ctime`, T.`comment`, (SELECT * FROM my_table GROUP BY user_id) AS `val` FROM my_table

Well, I have to say I'm not so familiar with SQL syntax, I haven't yet figured it out where error resides in this query string so far :(

Must I invoke raw() function in my model's manager instance, as is suggested in this answer?

model_class.objects.raw('SELECT T.* FROM my_table AS T GROUP BY `user_id` DESC LIMIT 10 OFFSET 0')

Or maybe I shouldn't use GROUP BY statement in the beginning?

There is a module for `GOUP_BY` - https://github.com/kako-nawao/django-group-by May be that will help. — Chiefir, May 07 '18 at 17:51

Yogesh Sharma · Accepted Answer · 2018-05-07T18:09:22.090

1

The easiest and simple way would be:

select * 
from my_table t
where ctime = (select max(ctime) from my_table where user_id = t.user_id);

However, you can use also use limit clause in inner query in case of more than one same date of user_id

select * 
from my_table t
where id = (select id 
            from my_table 
            where  user_id = t.user_id
            order by ctime desc 
            LIMIT 1);

edited May 07 '18 at 18:09

answered May 07 '18 at 17:59

Yogesh Sharma

49,870
5
26
52

Also I don't understand what you mean by "more than one same date of `user_id`", could you please explain this? – KaiserKatze May 07 '18 at 18:28

score 0 · Answer 2 · 2018-05-08T08:30:10.967

0

Oracle/PostgreSQL:

Try to use below SQL, it will fulfill your requirement.

select id,user_id,ctime,comment
from  (select   id,user_id,ctime,comment
                rank() over(partition by user_id order by ctime desc) rn
        from your_table_name
      ) s
where rn = 1;

edited May 08 '18 at 08:30

answered May 07 '18 at 17:58

What does `rank()` mean in your SQL? MySQL Workbench told me there is an errror :o – KaiserKatze May 07 '18 at 18:17
When I hover my cursor upon your SQL, MySQL workbench says "Syntax error: extraneous input found - expected 'closing parenthesis'" – KaiserKatze May 07 '18 at 18:24
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '() over(partition by user_id order by ctime desc) rn from your_table_nam' at line 3 – KaiserKatze May 07 '18 at 18:24

How to get latest records

2 Answers2