CODEWITHSUNDEEP
mysqlsqldjango
KaiserKatze
KaiserKatze

Reputation: 1579

How to get latest records

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?

Upvotes: 2

Views: 89

Answers (2)

user8406805
user8406805

Reputation:

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;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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

Upvotes: 1

Related Questions