Visual FoxPro SQL Queries - query with max date

Author

query with max date

Marc de Vries

2005-09-12, 7:26 am

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,n
ote;
FROM ;
trv_bv!rolhand;
GROUP BY Rolhand.dos_id;
INTO CURSOR maxhand

Anders

2005-09-12, 9:25 am

SELECT * FROM Rolhand AS R1WHERE rol_datum = ;
(SELECT MAX(roldatum) FROM Rolhand AS R2 ;
WHERE R1.dos_id=R2.dos_id)

-Anders

"Marc de Vries" <marc.de.vries@zonnet.nl> skrev i meddelandet
news:%23OFrKP4tFHA.2540@TK2MSFTNGP09.phx.gbl...
> 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,n
ote;
> FROM ;
> trv_bv!rolhand;
> GROUP BY Rolhand.dos_id;
> INTO CURSOR maxhand
>
>

Cindy Winegarden

2005-09-13, 3:24 am

Hi Marc,

Here's a little different way. You haven't said whether the combinations of
Dos_ID and Rol_Datum are unique. This code gets rows where there isn't any
larger date (Is Null).

Create Table RolHand (Dos_ID I, Rol_Datum D, cNote C(10))
Insert Into RolHand Values (1, {01/01/2001}, "111")
Insert Into RolHand Values (1, {01/01/2003}, "222")
Insert Into RolHand Values (1, {01/01/2002}, "333")
Insert Into RolHand Values (2, {01/01/2011}, "444")
Insert Into RolHand Values (2, {01/01/2010}, "555")

Select ;
RolHand.Dos_ID, ;
RolHand.Rol_Datum, ;
RolHand.cNote ;
From ;
RolHand ;
Left Join RolHand As RolHand2 On ;
RolHand2.Dos_ID = RolHand.Dos_ID And ;
RolHand2.Rol_Datum > RolHand.Rol_Datum ;
Into Cursor ;
MaxHand ;
Where ;
RolHand2.Dos_ID Is Null

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"Marc de Vries" <marc.de.vries@zonnet.nl> wrote in message
news:%23OFrKP4tFHA.2540@TK2MSFTNGP09.phx.gbl...
> 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

Sponsored Links