help in a simple query

[Ale]

I have a question I think you my help me out to aswer.
The following is a sample of my table

a b c d e f g h i j
1111 8 BEN 1 5 11 2003 9664 0 58637
166994 K URD 1 5 05 2002 13128 0 13128
166994 K URD 1 5 05 2002 0 1770 1358
166994 K URD 1 5 06 2002 0 1770 9588
166994 K URD 1 5 07 2002 0 1770 7818
166994 K URD 1 5 08 2002 0 1966 5852
166994 K URD 1 5 09 2002 0 5852 0
214272 4 RAU 1 3 06 2002 151 0 151
214272 4 RAU 1 3 06 2002 0 151 0
214272 4 RAU 1 5 10 2002 21054 0 21054


I would like to get the following

a b c d e f g h i j
1111 8 BEN 1 5 11 2003 9664 0 58637
166994 K URD 1 5 09 2002 0 5852 0
214272 4 RAU 1 3 06 2002 151 0 151
214272 4 RAU 1 3 06 2002 0 151 0
214272 4 RAU 1 5 10 2002 21054 0 21054

the oldest date ("f" = month; "g" = year) of each "a".
This seems simple, but i dont get the answer.
I hope u can help me out or at least let me know a site
where a can give me a hand to resolve this question.


best regards

 

[Gerald Stanley]

This would be easy if the table had an autonumber column.
If there was one in row k, then the query would be

SELECT * FROM YourTable T1 WHERE k IN (SELECT TOP1 k FROM
YourTable T2 WHERE T2.a = T1.a ORDER BY g,f)

Hope This Helps
Gerald Stanley MCSD

 

[John Spencer (MVP)]

One Two query solution (UNTESTED SQL samples follow)

SELECT A, Max(100*G + F) as MaxDate
FROM YourTable
GROUP BY A

Save that as qMax

SELECT *
FROM YourTable INNER JOIN qMax
ON YourTable.A = Qmax.A
AND YourTable(100*G+F) = qMaxDate

A one query solution is possible, but it will probably be significantly slower.