The problem of finding the maximum value by sqlalchemy fun.max

look at the requirements first:
the table structure is like this

Table
id   time    status  ob_id
1     12:50    2    a1
2     12:53    3    b1
3     13:01    3    a1

the requirements are based on ob_id, and the same description of ob_id is the same kind of data, but their states are different. (status)
needs to take out the data with the largest time, that is, for the above data, ob_id has the same A1 br ID is 1 and 3. I should take out
with the time of 13:01. This is what my query says:

res=db.session.query(Table.id,func.max(Table.time)).order_by(Table.time.desc()).group_by(Table.ob_id).all()

to check in this way, the maximum time can be obtained, but if you want to take out the data corresponding to the maximum time, it is not right to use id, here. My understanding is that after grouping, we can find the maximum value of the group, but we can"t get more data

.

I can"t check it with native sentences

 SELECT id, GROUP_CONCAT(time), MAX(time) FROM Table GROUP BY ob_id;

I don"t know if my understanding is correct, if so, is there any good way to achieve such a requirement?

Mar.30,2021

SELECT t1.* 
 FROM Table t1
   inner join (SELECT ob_id,MAX(time) mx_time FROM TABLE GROUP BY ob_id) t2 on t1.ob_id=t2.ob_id and t1.time=t2.mx_time

find python language sqlalchemy

Menu