need help in the followng query...

[asimasm]

i have a table say Tblx having columns col1(Primary Key and Autonumber),col2(Numeric),col3(date),col4(String)
Now i would like to find all those rows from this table that have the max of date column i.e col3 in a single query.
Sample data:
col1 col2 col3 col4
1 100 25/02/2001 String1
2 100 15/03/2001 String2
3 101 23/02/2001 strng4
4 102 01/01/2001 string5
5 100 17/01/2001 string6
6 106 23/02/2001 string7

Result:
2 100 15/03/2001 String2
3 101 23/02/2001 strng4
4 102 01/01/2001 string5
6 106 23/02/2001 string7
By now i have done this by first creating one view containing all the max(col3) and then refering this view in a 2nd query. But i want the results in a single query.

Plz help me if u can.
Thanks

 

[Tim1]

Hiya,

Try the following:

SELECT t1.col1,
t1.col2,
t1.col3,
t1.col4
FROM Tblx t1,
Tblx t2
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND t1.col3 = (SELECT MAX (col3)
FROM Tblx)
AND t1.col4 = t2.col4.

Should give you your desired result

 

[asimasm]

Hi
I had posted this question early but the answer that Tim1 had posted would not solve my problem. Bcz i need all the max of a col3 grouped by col2. So as the example i had given in my question:
col1 col2 col3 col4
1 100 25/02/2001 String1
2 100 15/03/2001 String2
3 101 23/02/2001 strng4
4 102 01/01/2001 string5
5 100 17/01/2001 string6
6 106 23/02/2001 string7

Result:
2 100 15/03/2001 String2
3 101 23/02/2001 strng4
4 102 01/01/2001 string5
6 106 23/02/2001 string7

i have a query that gives me results only col3 and col2 but i dont know how to give a comple row. My query is:

select max(col3),col2
from TBlx
group by col2

this query gives me only col2 and col3. Now i want a single query that gives me a complete row. As i have mentioned in my first post that i already have a two query solution. But i need the results using a single query.

Thanks

 

[Tim1]

Ok,

Well the query you need is:

SELECT col1,
col2,
max(col3),
col4
FROM TBlx
GROUP BY col1,
col2,
col4

 

[asimasm]

Hi again
Thanks once again Tim1 for u'r prompt reply. But this query also does not solve my problem. The example i had sent posted earlier was a temp one. My actual table contains abut 10 different columns. So i cannot group on all of them. Bcz they return wrong answer. I can only group on the column i had mentioned in my previous post.



Thanks once again

 

[Cagliostro]

asimasm,
Look below, maybe it'll help:
select TBlx.* from TBlx inner join
(select col2, max(col3) x from TBlx group by col2) t on
TBlx.col2 = t.col2 and TBLx.col3 = t.x John Fill

 

[asimasm]

Thanks John That solved my problem.