How to group rows and select top 1

Question

I am using a SQL Server CE database. I have a students fee table like :

Table dues:

Erno  |   Date     |  Due   
---------------------------
1       1-Jan-2016    220
1       1-Mar-2016    200   
1       1-Apr-2016    210   
1       1-May-2016    200
2       1-Jan-2016    250   
2       1-Feb-2016    300   
2       1-Mar-2016    220   
2       1-Apr-2016    200   
3       1-Jan-2016    300
3       1-Feb-2016    150   
3       1-May-2016    400
3       1-Jun-2016    300

where ErNo is Enrollment no. and Date is deposit date and Due is balance of amount or fee up-to that date.

I want to get the latest record of every Erno or Student.

Like :

Latest Due information :

   Erno        Date       Due
     1      1-May-2016    200   
     2      1-Apr-2016    200
     3      1-Jun-2016    300

Is there any SQL Server CE query to do so ?

Answer 1

Try

  select fee.erno,fee.date,fee.due from dues fee 
    join 
    (select erno, max([date]) maxdate
        from dues group by erno) mx 
    on fee.erno = mx.erno and fee.date = mx.maxdate
    order by fee.erno