0

I have student documents table that store data with class and stream foreign id as following.

enter image description here

I want to fetch latest document from student_documents that is grouped by class and stream.

Current query and result is as below.

SELECT this.* FROM ( 
    SELECT * from student_documents 
    where status != 'D' 
    order by document_id desc ) this 
WHERE 1 AND(this.status!='D') 
group by fk_class_id, fk_stream_id 
order by document_id desc 
limit 0,20

enter image description here

Here Iam expecting document_id 2,3 and 5 and getting 1,3 and 4.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Alfiza malek
  • 994
  • 1
  • 14
  • 36
  • ORDER BY apllied in the subquery is always lost in outer query. If your subquery rowset is not order-dependent (no LIMIT, user-defined variables usage, etc.) then you may (or even must) remove ORDER BY clause from the subquery code. – Akina Mar 18 '21 at 06:57
  • What's the point of using `GROUP BY` without any aggregation functions? – Barmar Mar 18 '21 at 07:00

0 Answers0