MSaccess 2007 SQL complex query

Question

I have the following table columns and values...

ColA, ColB, ColC
b, 90, 1
p, 95, 5
p, 100, 6
p, 99, 6
p, 98, 6
b, 94, 5
b, 93, 1
b, 92, 3
o, 89, 3
b, 88, 4

I need the following result set:

ColA, ColB, ColC
b, 90, 1
b, 93, 1
p, 95, 5
o, 89, 3

Essentially, this is the lowest value for ColC where ColA is the same. So the lowest value for all the b's is 1, and it occurs in two rows. The lowest value for all the p's is 5, and the lowest value for all the o's is 3. ColB is a value to join on another table. So I do need a query that will join on another table over ColB.

Thanks.

Answer 1

Try This

select mainTable.* from abcTable as mainTable inner join 
(select t.colA,min(t.colC) as minColC from  abcTable as t group by t.ColA) as minimumTable
on mainTable.colA=minimumTable.ColA and mainTable.colC=minimumTable.minColC

Answer 2

Here you go!

with r1 as
(select *
,rank() over (partition by ColA order by ColC) fix
from aba
)

select * from r1 where fix = 1