How to select top record of same group?

Now I am with MS SQL Server 2008. I would like to know if it is possible to select top record of same group.I have 3 fields in my table. Here is my table 开发者_如何学C

ID      GroupNo     FieldOfInterest  
0001    G1                  Football  
0002    G2                  Football  
0003    G2                  Basketball
0004    G3                  Golf
0005    G3                  Swimming  

I wanna select the record from this table like that

ID      GroupNo             FieldOfInterest  
0001    G1                  Football  
0002    G2                  Football  
0004    G3                  Golf  

How can I do this? Please help me .

With Regards

SELECT t1.*
FROM table AS t1
INNER JOIN (
    SELECT MIN(ID) AS MinID
    FROM table
    GROUP BY GroupNo) AS t2
ON t1.id = t2.MinID

Assuming the "top record" means the record with the samllest ID, as you have not provided an order.

SELECT    ID,
          GroupNo,
          FieldOfInterest
FROM      YOUR_TABLE a,    
          (SELECT    MIN(ID) AS ID
           FROM      YOUR_TABLE 
           GROUP BY  GroupNo) b
WHERE    a.ID = b.ID;

Here, I am guessing that by top record you meant records with the smallest ID value.

The SQL Server ROW_NUMBER() function can solve this for you:

create table interests (
    ID  int,
    GroupNo varchar(2),
    FieldOfInterest varchar(20)
)

insert into interests
values (1, 'G1', 'Football'),
    (2, 'G2', 'Football'),
    (3, 'G2', 'Basketball'),
    (4, 'G3', 'Golf'),
    (5, 'G3', 'Swimming');

with cte as (
    select ROW_NUMBER() OVER (PARTITION BY GroupNo ORDER BY ID) as row, *
    from interests
)
select c.ID, c.GroupNo, c.FieldOfInterest
from cte c
where row = 1