homepage Welcome to WebmasterWorld Guest from 54.145.252.85
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Select Distinct then Minimum.
IntegrityWebDev

5+ Year Member



 
Msg#: 4233813 posted 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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4233813 posted 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved