Help with a SQL query

Question

How would I go about this? I have 3 rows like so:

ID         THREAD         POST
1            1              Hello World
2            2              Hello galaxy
3            2              Hello people

I need to return ID, Thread, and Post once for each thread only using the lowest ID.

something like:

SELECT * FROM table WHERE THREAD DISTINT AND ID = MIN(ID)

Edit: To be more clear in this example it should only return the rows for 1 and 2 because 3 isn't the lowest ID with a Thread of 2

Edit 2: it needs to return both rows 1 and 2.

Answer 1

In SQL Server 2005 and beyond:

SELECT t.Id, t.Thread, t.Post
FROM 
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Thread ORDER BY Id) as RowNumInThread
FROM table
) t
WHERE t.RowNumInThread = 1
;

or

SELECT t.*
FROM 
(
  SELECT Min(Id) as MinId
  FROM table
  GROUP BY Thread
) tmin
JOIN
table t   ON t.Id = tmin.Id
;

Answer 2

select id, thread, post
from t
inner join (
select min(id)
from t
group by thread) subq on t.id = subq.id

Answer 3

You can try something like

SELECT  t.*
FROM    Table t INNER JOIN
        (
            SELECT  MIN(ID) MINID,
                    Thread
            FROM    Table
            GROUP BY  Thread
        ) sub   ON  t.ID = sub.MINID 
                AND t.Thread = sub.Thread