Wiki Home

SQL Query , grouping , latestrecord


Namespace: WIN_COM_API
Hi.

I have a list of files from a few different directories.
I pulled that listing into a table.
I already know there are some duplicate filenames.

I made a program to replicate the data I'm dealing with.

I need to pick out the lines from the table that have the latest TDATETIME.

I can sort of do this.

I do get 01/22/2008 11:57:00 AM and 04/27/2009 04:16:00 PM but the PK's are NOT: _3H60WOPN4 and _3H60WOPR5

I am able to get the correct (latest) TDATETIMEs for each group but it gets the last record for each group.

Here is the code that will get you a table of what I'm selecting from:

SET STRICTDATE TO 0
SET ENGINEBEHAVIOR 70


CREATE TABLE mydata( cdate c(10),ctime c(10), cdivdir c(20), tdatetime t, PK C(10))

IF USED('mydata')
ELSE
	USE mydata IN 0
ENDIF

=INSERT_DATA ("01/22/2008","  11:57 AM","VIEWS.FXP           ",{01/22/2008 11:57:00 AM},"_3H60WOPN4")
=INSERT_DATA ("01/22/2008","  10:57 AM","VIEWS.FXP           ",{01/22/2008 10:57:00 AM},"_3H60WOPPA")
*=INSERT_DATA ("","","",{},"")
=INSERT_DATA ("12/04/2008","  02:12 PM","WS_FTP.LOG          ",{12/04/2008 02:12:00 PM},"_3H60WOPQ2")
=INSERT_DATA ("04/27/2009","  04:16 PM","WS_FTP.LOG          ",{04/27/2009 04:16:00 PM},"_3H60WOPR5")
=INSERT_DATA ("01/09/2009","  09:05 AM","WS_FTP.LOG          ",{01/09/2009 09:05:00 AM},"_3H60WOQ2N")




PROCEDURE INSERT_DATA
	LPARAMETERS PC_CDATE, PC_CTIME, PC_CDIVDIR, PT_TDATETIME, PC_PK

	INSERT INTO mydata (CDATE, CTIME, CDIVDIR, TDATETIME, PK);
			VALUES (PC_CDATE, PC_CTIME, PC_CDIVDIR, PT_TDATETIME, PC_PK)

ENDPROC


_3H60WOPN4 is on the data line that has the latest TDATETIME and my selects can get that TDATETIME but all the other fields are from the line below it.


_3H60WOPR5 is on the data line that has the latest TDATETIME and my selects ... below it.

How do I write a select so it will get the entire PN4 row and the entire PR5 rows.

The results of the query that I'm trying to design will be used to relate back to this table so i can mark in a different column which are the latest in each group.

Thanks for any help you can provide!

With some trepidation and +++ caution regarding many possible issues (particularly involving mydata rows with duplicate PK values) the following seems to work in VFP9:
SELECT * FROM mydata a ;
JOIN (SELECT cdivdir, MAX(tdatetime) FROM mydata GROUP BY cdivdir) b ;
ON a.cdivdir = b.cdivdir AND a.tdatetime = b.max_tdatetime
( Topic last updated: 2012.04.13 08:06:44 AM )