SQL Query with ORDER BY

I have a table in my SQL database that looks like:

id    type      radius
-------------------------
1     type1     0.25
2     type2     0.59
3     type1     0.26
4     type1     0.78
5     type3     0.12
6     type2     0.45
7     type3     0.22
8     type3     0.98

I am having trouble figuring out how to define a SELECT query which returns the lowest radius for each type.

In other words, the results I am looking for would be:

Results:

id    type      radius
-------------------------
1     type1     0.25
6     type2     0.45
7     type3     0.22

I assume I use ORDER BY to开发者_开发问答 order the radius from lowest to highest and grab the lowest. However, I also assume I need to use DISTINCT on the type, but I can't figure out how to pull this off.

Any expert SQL'ers out there have any idea of this type of SELECT query is possible? Any help is much appreciated!

Many thanks, Brett

You want to group by type, and then get the minimal radius from that type, and sort it by type

SELECT type, MIN(radius) FROM table
GROUP BY type
ORDER BY type

If you want the id to go with the min, you cannot do it as I first typed: that id is a semi-random Id for type. Sadly, you'll have to settle for (from the top of my head)

SELECT t1.id,t1.type,t1.radius FROM table t1 
WHERE radius = ( 
     SELECT MIN(radius) FROM table
     WHERE radius = t1.radius
   )

(final edit: you've got to test that last one, basic warning is that in the first query you cannot reliably fetch the Id, because it will NOT belong specifically to the type that has the lowest radius)

Try using

SELECT min(radius) FROM table GROUP BY type

min will return the minimum value as distinct will return distinct values but group by allows you to perform the query on distinct fields.

check out http://www.w3schools.com/sql/sql_groupby.asp (and also http://www.w3schools.com/sql/sql_quickref.asp)

You have to combine min and GROUP BY

I believe this should work:

SELECT t1.id,t1.type,t1.radius FROM table GROUP BY type ORDER BY radius

Since order by defaults to ascending the minimum value should be the one shown.