Simple Query

[Te4t0n]

Hi, I have a table:Table1 Co1 is an ID (auto increment)Co2 is a DateCo3 is TextExample Data:Col1 col2 col31 01/12/2008 812/112 30/12/2008 812/223 18/12/2008 812/334 10/12/2008 812/335 21/12/2008 812/226 06/12/2008 812/227 04/12/2008 812/118 01/12/2008 812/119 02/12/2008 812/11I would like for a query to select all three columns and return only rows that have the highest date the col3.7 04/12/2008 812/112 30/12/2008 812/223 18/12/2008 812/33I hope this is enough information. P.S i'm using Access, so just SQL, WHERE, IN, Subqeries etc would be great thks.

[lugos]

It may not be as simple as one would think. You can start here, here, here, and here.

[aalbetski]

This should work (don't know about Access, works in SQL Server)

select * from table1 where Col1 = (select max(Col1) from table1)union allselect * from table1 where Col2 = (select max(Col2) from table1)union allselect * from table1 where Col3 = (select max(Col3) from table1)[sql]

[Te4t0n]

Thank you for replying,I will try your solutions, i think an inner join on a subquery with where statements will work best, it's really close to all my solutions i tried.I'll pot back tomorrow between 9am and 10am.

[Te4t0n]

Hi, the union all didn't display the required records.But using Inner Join worked great, except it returned one record twice?