help in a simple query

  • Thread starter Thread starter Ale
  • Start date Start date
A

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
 
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
 
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.
 
Back
Top