SQL query to find max date and and corresponding field

SELECT DISTINCT ColA, ColB, ColC
FROM Table2 AS A WHERE ColB = (SELECT MAX(ColB) FROM Table2 AS B WHERE A.ColA = B.ColA)

i think the code below some help

select table1.cola as colb , table2.colb,table2.colc 
from table1,table2 
where table1.cola=table2.cola
group by table1.cola,table2.colb,table2.colc
having table2.colb=max(table2.colb)

Select allOpen in new window

corrected mistyping

select table1.cola as cola , table2.colb,table2.colc 
from table1,table2 
where table1.cola=table2.cola
group by table1.cola,table2.colb,table2.colc
having table2.colb=max(table2.colb)

Select allOpen in new window

Hello,

I tried both  solutions and neither seem to work.  The first one I could not even get to work.  This query is part of a larger query and I cant figure out how to add it in.

As for the second solution, I get too many results.  I should get 70 records but I get 287.

I have attached the two tables in question.  

If you look in table1, you will see 70 records, with a unique column called STDinv_ID.  Table 2 has many records with the same column STDinv_ID but there are many as opposed to unique.  When I run solution 2, I get 283 results where I am looking for 70 records.

Am I doing something wrong?  Can you try it with these 2 tables?
Table1.xls
Table2.xls

I have an update.  I managed to get solution working...sort of.  I have more records than I should but I know why and could use some help resolving it.

The problem is that the query does do what it should, except if there are 2 exact date/times for a group (lets say group1). then it returns 2 records.  For instance.

Table 1                           Table2
ColA                                ColA             ColB             ColC
Group1                           Group1       01/01/08         Screening
Group2                           Group1       01/01/08         Closout
Group3                           Group1       03/01/08         Initiation

The result table looks like this

Table3
ColA              Colb              ColC
Group1         01/01/08         Screening
Group1         01/01/08         Closeout

but I need to look like this,
ColA              Colb              ColC
Group1         01/01/08         Closeout

I only need 1 result, but I need it be logical.  For example, if there are 2 results where one is a Screening and the other is a Closeout, then choose Closeout.  Is this possible to do?

The above example could very well happen where a person is screened and closed out in the same session.  However, the most recent "transaction" in this case is the closeout, not the screening.  

Thanks again for your help.

If you need the solution I can provide one that will work exactly as you need.

select * from table2 where colb in
(select max(colb) from table2 group by cola);

i think there is no easy way to do if there is no way to order ColC  in logicaly

when you find the formula make the order you can
extending  having caluse with table2.colc aggregates work for you
Ex;
...
having table2.colb=max(table2.colb)
           and table2.colc = min(table2.colc) -- select only closout because "closeout" < "screening"  in
                                                                      text  comparasion

or

..
having table2.colb=max(table2.colb)
            and datalen(tabe2.colc) = 8   -- select only closeout because it'length 8 but "screening" has length 9

At this point I am willing to pay someone to write this query for me.  Who wants to do it .....and am I allowed to do this on Experts-Exchange?

write at myusername at yahoo

how about this:

select cola, max(colb) colb, max(case colc when 'Screening' then 1
			when 'Closout' then 2
			when 'Initiation' then 3 end)
id from table_2 group by cola

Select allOpen in new window

>>and am I allowed to do this on Experts-Exchange?<<
No.

Thanks gentlemen, both of your responses helped me solve the problem