Display only the first row per match

Question

I have a table (AreaPartners), and I want to match only the first "Name" record in each group, ordering the "ID" grouped by "Area". So for the table below:

Area Name ID AB ISmith 748 AB AWood 750 AB HArcher 751 AB DMunslow 753 AB DCornelius 754 BH MLee 301 BH NMcClean 307 BH DMiles 309 BH LPayze 325 BH MPinnock 329 

I want to return the ISmith results for AB and MLee for BH.

How should I do it? I believe this is due to the Group By function, but I cannot make it work in my life.

Answer 1

Try the following:

 SELECT yourTable.Area, yourTable.Name FROM yourTable INNER JOIN ( SELECT MIN(Id) AS MinId FROM yourTable GROUP BY Area) M ON yourTable.Id = M.MinId 

Answer 2

Update due to comment (There is no table variable, and Partition over is not an MS access statement). You can also do this using the IN statement:

 SELECT yourTable.Area, yourTable.Name FROM yourTable WHERE yourTable.Id IN ( SELECT MIN(tbl.Id) AS MinId FROM yourTable as tbl GROUP BY tbl.Area ) 

In MSSQL you can write this:

 DECLARE @tbl TABLE ( Area VARCHAR(100), Name VARCHAR(100), ID INT ) INSERT INTO @tbl SELECT 'AB','ISmith',748 UNION ALL SELECT 'AB','AWood',750 UNION ALL SELECT 'AB','HArcher',751 UNION ALL SELECT 'AB','DMunslow',753 UNION ALL SELECT 'AB','DCornelius',754 UNION ALL SELECT 'BH','MLee',301 UNION ALL SELECT 'BH','NMcClean',307 UNION ALL SELECT 'BH','DMiles',309 UNION ALL SELECT 'BH','LPayze',325 UNION ALL SELECT 'BH','MPinnock',325 ;WITH CTE AS ( SELECT RANK() OVER(PARTITION BY tbl.Area ORDER BY ID) AS iRank, tbl.ID, tbl.Area, tbl.Name FROM @tbl AS tbl ) SELECT * FROM CTE WHERE CTE.iRank=1