Select Distinct then Minimum.

Forum Moderators: open

Message Too Old, No Replies

Select Distinct then Minimum.

IntegrityWebDev

9:17 pm on Nov 22, 2010 (gmt 0)

I am using c#/MS-SQL and have a table similar to this:


id type image
--------------
10 AAAA a1.jpg
11 AAAA a2.jpg
12 BBBB b1.jpg
13 BBBB b2.jpg

I need to select the distinct type, AAAA or BBBB and out of those I need to select one row where the id is lowest.

So for the above example I need to return rows 10 and 12 (the lowest id of each distinct category).

id is always unique.

Any thoughts on this? I need to return all columns.

Thanks!
Chris

LifeinAsia

10:07 pm on Nov 22, 2010 (gmt 0)

Off the top of my head, I think this will work:
SELECT Table.*
FROM (SELECT Type, MIN(ID) AS ID FROM Table GROUP BY Type) AS a INNER JOIN Table ON a.ID=Table.id