Hi
I have a table
table name : shtrack
columns :
contnum (STRING)
actdate (DATE)
actseq (INTEGER)
acttype (STRING)
there can be multiple rows with the same data for contnum, but I want a
query that will give me the newest row by date (actdate) for each contnum
entry. I then want to further refine my query so that the rows returned
match a particular entry for the 'acttype' column, but the returned rows
must only be where the acttype value of the newest row is that particular
entry.
i.e.
SELECT Distinct(ContNum), Max(ActDate), Max(ActSeq) FROM shtrack HAVING
acttype='11' GROUP By Contnum, ActDate"
However the above gives me all rows that contain acttype='11', I am not
wanting rows where there is another value on a newer date.
CONTNUM ACTTYPE ACTSEQ ACTDATE
============================================
a 11 0 2001-01-01
a 07 0 2001-01-15
b 11 0 2001-01-01
c 13 0 2001-01-14
c 11 0 2001-03-20
c 07 0 2001-05-10
in the above example, the result would only return the "b" row as both the
"a" & "c" rows have entries where the date is newer than the 11 entry.
Hope I am explaining it clearly
Regards
Warren
database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Warren van der Merwe
Software Director
PRT Trading (Pty) Ltd t/a RedTie
Durban, South Africa
Cell (+27-83) 262-9163
Office (+27-31) 767-0249
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
