return the latest date of records

[de1458]

is vfp5.0 has build in syntax to return the last modified entries amount records,
for example, table "order", primary key "client_num", and I want to query only the last activities,
return: 0001, 1/19/2002
0002, 2/3/1995

table: order
+-----------------------------------+
| client_num | date_orderd |
|0001 | 1/2/2000 |
|0001 | 2/2/2000 |
|..... | ..... |
|0001 | 1/19/2002 |
|0002 | 1/3/1994 |
|0002 | 2/3/1995 |
| .... | ...... |
+-----------------------------------+

TIA
Dennis

 

[jimoo]

Hi Dennis,

If I am following you correctly, you should be able to do it using a DISTICNT Clause and GROUP DESCENDING

SELE DISTINCT key, datefield from customer ORDER by key, datefield DESCENDING GROUP by datefield

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[de1458]

Thanks Jim, let me play around with it

 

[BPeisch]

The MAX function will do this for you:

Select Client_Num, MAX(Date_Orderd) as LastDate, ;
   from Order ;
   group by Client_Num

BTW, Order is a terrible name for a table. It's a reserved word in VFP and can get you into trouble, especially in SQL Select statements.



-BP

http://www.peisch.com

 

[jimoo]

The problem with the MAX fuction is that it's not rushmore optimized. Therefore, it is slower on most tables.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[BPeisch]

The problem with the MAX fuction is that it's not rushmore optimized. Therefore, it is slower on most tables.

Slower than what? Distinct isn't exactly fast either, and your solution returns all combinations of the PK and date instead of just the max date, so the result set is significantly bigger. If you're running against something like SQL Server, that's going to be a lot slower.

FWIW, I just ran some timing tests on a table with 1,276,844 records. Using the Max function was consistently just under 20 seconds. Using distinct was consistently just over 20 seconds.




-BP

http://www.peisch.com

 

[rob444]

Put up an index/tag on date_ordered.
If it is ascending, a "go bottom" will give you the record with the latest date, if it is descending a "go top" will give you the record with the latest date.
No faster way possible.

Rob.

 

[jimoo]

Hi Barbara,

I didn't mean to hit a nerve when I said Max() was not optimized.

Running a function as part of any query, when it is not required, is usually slower - even than distinct clause. I have played with this a lot and that has been my experience anyway.

Rob is on the right track. The real key to performance is the index.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[BPeisch]

I still belive using MAX is better in this situation. You don't pull as many records across the network that way, and even my timing tests on local data were faster.

Ultimately, I think it's up to de1458 to try it both ways to determine what works best.



-BP

http://www.peisch.com

 

[jimoo]

As ramani has said before, "...there is more than one way to do it in fox" or something like that anyway.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[de1458]

thank you so much guys and I got more than I initialy asked for, I will try both of them, for my situation, table aren't big, but a lot to look for, more than 30 tables, a little bit on optimize might help,