Select using MIN question

[showdown]

Hello aces-

I'm not a very proficient coder- I spend most of my time working within CMS's and designing but occasionally I have to step outside and find a solution- here's one of those times...

I have a table with the following columns:

permit, year, category, class, age, license, points, level

In this table there will be rows where the license is the same as well as other columns. In these instances where the license is the same the level column will be different. IE:

id,permit, year, category, class, age, license, points, level
0, 123, 2009, 1, master, 30+, 215999, 60, 1
1, 123, 2009, 3, master, 30+ 215999, 50, 3

I would like to select all the rows in the table, filtering on year and permit, where the points field is not null and most importantly where there are duplicates according to license, select the row with the lowest value in level.

So from that example I want to select the first row with id 0.

This is the code I "think" is a start... I guess I'm just not totally sure on the MIN part of the code...

SELECT    year,  category,  class,  age,  license,  points,   Min(level) AS min_level FROM  table WHERE  points IS NOT NULL AND year = 2009 AND permit = 123 GROUP BY  class, category, age

Thanks in advance for any tips and or suggestions!

-Julian

[Weedpacket]

Moved to the correct forum.

[Lars_Berg]

select year,  

       category,  

       class,  

       age,  

       license,  

       points,   

       level as min_level
  from t as biss
 where points is not null
   and year = 2009 
   and permit = 123 
   and level in
       (select min(level)
          from t
         where t.license = biss.license)

[showdown]

Brilliant!,

Thank you so very much! Now I'll just have to learn why the code you posted works... but that's the fun part!

-Julian