select max date 1

[LloydDobler]

Hi all, hope someone can help me with this one...
I have a view in which I need to display a max date. Here's the structure

EE# TrMod CompDate Vendor
1234 WHMIS 2/4/4 Joe's training
1234 1st Aid 2/5/6 BTC
1234 WHMIS 2/4/6 TCBY

In this case, of the 2 WHMIS trainings, I would only want the 2/4/6 to show (along with all other training). I know I could do a select max with a group by, but they have different vendors, so it would show as 2 different lines still.

any thoughts?

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[bborissov]

I didn't understood did you want second record or not? But here a simple query that wuill return you the record(s) which have MAX() date for every EE#

SELECT MyTable.*
FROM MyTable
INNER JOIN (SELECT EE#, MAX(CompDate) AS CompDate
                   FROM MyTable
                   GROUP BY EE#) Tbl1
ON MyTable.EE# = Tbl1.EE# AND
   MyTable.CompDate = Tbl1.CompDate

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[randy700]

How about...

SELECT EE#, TrMod, CompDate, Vendor
FROM [i]table[/i]
WHERE CompDate IN
(SELECT Max(CompDate), TrMod
 FROM [i]table[/i]
 GROUP BY TrMod

Note: not tested.......


Randy

 

[LloydDobler]

my apologies Boris, I've left out one very key aspect, in the same view, I can have an entry without a compdate, or else Boris your idea would have worked (I actually tried from another post of yours)
so the revised view would look somthing like below

EE# TrMod CompDate Vendor
1234 WHMIS 2/4/4 Joe's training
1234 1st Aid 2/5/6 BTC
1234 WHMIS 2/4/6 TCBY
1234 fire

and my goal is to have only 1 WHMIS, the first aid, and the fire training. Once again, sorry for my mistake.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[bborissov]

SELECT MyTable.*
FROM MyTable
INNER JOIN (SELECT EE#, TrMod, MAX(CompDate) AS CompDate
FROM MyTable
GROUP BY EE#, TrMod) Tbl1
ON MyTable.EE# = Tbl1.EE# AND
MyTable.CompDate = Tbl1.CompDate AND
MyTable.TrMod = Tbl1.TrMod
[/code]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LloydDobler]

Same results. The TRMod 'Fire' needs to show up even though it hasn't been completed.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[LloydDobler]

Got it, added an (OR compdate is null) statement for the date. Thanks for the help BoBo! have a star!

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."