I have list of unique ids. I want to fetch records from table VideoStat for the list of ids where timestamp is max. The table have multiple records with same id.
for example:
Video stats:
'657','228492','36423418','8982','5059','2020-06-03 17:18:27' '656','942203','354386731','118191','51575','2020-06-03 17:22:01' '656','942203','354387722','118191','51575','2020-06-03 17:22:25'
If i have id=[656,657] then it should return row 1 and 3
I am getting below error: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: IN near line 1, column 17 [SELECT DISTINCT IN (:ids) * FROM com.sociopool.entity.VideoStat WHERE timestamp = (SELECT MAX(timestamp)]
@Override
public List<VideoStat> getActiveVideo() {
Session session=sessionFactory.getCurrentSession();
//Get list of id from video table where is_active=1
List<String> ids = session.createQuery("SELECT id from Approval where is_active=:activeStatus").setParameter("activeStatus", 1).getResultList();
System.out.println("Active id list "+ ids);
//Get max(timestamp) stat for each id
String getStatQuery = "SELECT DISTINCT IN (:ids) * FROM VideoStat WHERE timestamp = (SELECT MAX(timestamp)";
List<VideoStat> videoStatList = session.createQuery(getStatQuery).setParameter("ids",ids).getResultList();
System.out.println("Video stats list " + videoStatList);
return videoStatList;
}
You need a query like this
List<VideoStat> videoStatList = session.createQuery(
"SELECT s FROM VideoStat s WHERE s.id IN :ids AND s.timestamp = ALL (SELECT MAX(sub.timestamp) FROM VideoStat sub WHERE sub.id IN :ids)"
).setParameter("ids",ids).getResultList();