Search code examples
sqlms-access-2007

MSaccess 2007 SQL complex query

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.

Solution

  • 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