Welcome to WebmasterWorld Guest from 54.197.171.28

Forum Moderators: open

Message Too Old, No Replies

Select Distinct then Minimum.

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

5+ Year Member



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
10:07 pm on Nov 22, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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