select the first records of each group

  • Hello,

    I have a query that is using group by. I then want the first records of each group.

    I know in Access I can use first to have the first records. but in Sql server I cannot.

    The Min doesn't work as I expected to do. If there is only one min, I think it will work. But now I want two fields.

    This will pull wrong records.

    Please help.

    See below query:

    SELECT TOP (100) PERCENT [Last Name], [First Name], [Building Desc], MIN([Sub Plan Desc]) AS Expr1, MIN([Sub Plan Cat Desc]) AS Expr2

    FROM dbo.Benefit

    GROUP BY [Last Name], [First Name], [Building Desc]

    ORDER BY [Last Name], [First Name], [Building Desc], MIN([Sub Plan Desc]), MIN([Sub Plan Cat Desc])

  • In SQL 2005 and later, I usually use Row_Number and a CTE to do that.

    Something like this:

    ;

    WITH CTE

    AS (SELECT [Last Name],

    [First Name],

    [Building Desc],

    [Sub Plan Desc],

    [Sub Plan Cat Desc],

    ROW_NUMBER() OVER (PARTITION BY [Last Name],

    [First Name], [Building Desc] ORDER BY [Sub Plan Desc], [Sub Plan Cat Desc]) AS Row

    FROM dbo.Benefit)

    SELECT [Last Name],

    [First Name],

    [Building Desc],

    [Sub Plan Desc],

    [Sub Plan Cat Desc]

    FROM CTE

    WHERE Row = 1 ;

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks,

    but I still get multiple records for each person.

    I only want the first records

  • Do you have a "Person ID" or something of that sort, or just first and last name?

    If it's just by name, then you can change the Partition By portion of the Row_Number function so it just gets each person once.

    (If it's just by name, please consider redesigning the database, if you're in a position to do so. Otherwise, the moment you have two "John Smith"s in the database, all kinds of things will break.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Ann Cao (4/7/2010)


    Thanks,

    but I still get multiple records for each person.

    I only want the first records

    Based on your original query you would expect to get multiple rows per person if they have been in/assigned to more than 1 building. If you want only 1 row per person then you would need to eliminate [building desc] from the Group By/Partition By.

  • Thanks.

    This is actually not a table, just an excel file from a staff. I imported into the sql server to do the data manipulation.

    I am able to do it by using the following queries, it works.

    SELECT *

    FROM (SELECT ROW_NUMBER() OVER (PARTITION BY dbo.Benefits.[Last Name], dbo.Benefits.[First Name],

    dbo.Benefits.[Building Desc]

    ORDER BY dbo.Benefits.[Sub Plan Desc], dbo.Benefits.[Sub Plan Cat Desc]) AS Seq,

    dbo.Benefits.[Last Name], dbo.Benefits.[First Name], dbo.Benefits.[Building Desc],

    dbo.Benefits.[Sub Plan Desc], dbo.Benefits.[Sub Plan Cat Desc]

    FROM dbo.Benefits) t

    WHERE Seq = 1

  • I think logically that Gus's query using a CTE and your query using the derived table are the same.

  • I tried you query again, it works this time.

    Thank you!

  • Jack Corbett (4/8/2010)


    I think logically that Gus's query using a CTE and your query using the derived table are the same.

    They are.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply