| Visual FoxPro Tips |
| |
| Using SQL to get latest date
per data type |
| |
| Suppose you have this data: |
| |
| pkid |
date |
ElemType |
| |
|
|
| 1 |
7/1/2000 |
1 |
| |
|
|
| 2 |
1/1/2001 |
1 |
| |
|
|
| 3 |
1/1/2000 |
1 |
| |
|
|
| 4 |
5/1/2000 |
2 |
|
| |
How would you write a SQL statement
to get rows with the last date per ElemType?
Example: |
| |
| pkid |
date |
ElemType |
| |
|
|
| 2 |
1/1/2001 |
1 |
| |
|
|
| 4 |
5/1/2000 |
2 |
|
| |
| Here is the answer in VFP code: |
| |
* First let's generate some
sample data
create cursor myGrouper (Ino i, ddate d, irec i)
for ix = 1 to 20
insert into myGrouper ;
values (int(rand()*5), date()-int(rand()*100), ix)
endfor
select * from myGrouper order by 1,2 &&
Check manually |
| |
* Here comes the answer
select * from myGrouper A where ddate = ;
(select max(ddate) from myGrouper B where a.ino
= b.ino) ;
order by ino |
| |
* This also works
update myGrouper set irec = irec*100 ;
where ddate = ;
(select max(ddate) from myGrouper B where myGrouper.ino
= b.ino) |
| |
| This tip is provided to you
by Foxy Classes.
For more Visual FoxPro tips, click
here. |
| |
