query with max date

[MdVries]

Table: ROLHAND

dos_id rol_datum note
--------+-------------+----------
1 01-01-01 111
1 01-01-03 222
1 01-01-02 333
2 01-01-11 444
2 01-01-10 555


I want to select the rol_datum and note record with the highest date order by dos_id


Output:

1 01-01-03 222
2 01-01-11 444

This output I want to place into the table MAXHAND

How can I realise this, can someone help me?

This code does not exactly do the job


SELECT Rolhand.dos_id, MAX(Rolhand.rol_datum),Rolhand,note;
FROM ;
trv_bv!rolhand;
GROUP BY Rolhand.dos_id;
INTO CURSOR maxhand

 

[JRB-Bldr]

When you say that your example SQL Query "does not exactly do the job", what is not working?

Is the field Rol_Datum a Date field or some other type?

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[TheRambler]

Hi,

I guess he means he is getting wrong note values for each dos_id, max(rol_datum) group, like these:

1 01-01-03 333
2 01-01-11 555

If so, I would do it with two select statements:

SELECT Rolhand.dos_id, ;
 MAX(Rolhand.rol_datum) as maxdate ;
 FROM rolhand ;
 GROUP BY Rolhand.dos_id;
 INTO CURSOR temp

SELECT temp.dos_id, ;
 temp.maxdate, ;
 rolhand.note ;
 FROM temp, rolhand ;
 WHERE temp.dos_id = rolhand.dos_id and ;
 temp.maxdate = rolhand.rol_datum ;
 INTO CURSOR maxhand