first element in sublist

  • I have a problem. I have table:

    Group Subgroup

    1 11

    1 12

    1 13

    .....

    2 21

    2 22

    2 23

    ......

    3 31

    3 32

    ......

    How to write sql statement that choose first element from every group ?

    1 11

    2 21

    3 31

    Don't use MIN(Subgroup) because in real solution I must use different

    order than subgroup

    I may use:

    select distinct

    group,

    (select top 1 subgroup from table t where table.group=t.group order by subgroup) as subgroup

    from table

    but it isn't usable if I want to list more fields. In MSAccess there is

    FIRST function witch is useful in it

    select group, first(subgroup) from table group by group

  • select A.* from table A

    inner join

    (

    select distinct

    group,

    (select top 1 subgroup from table t where table.group=t.group order by subgroup) as subgroup

    from table as A

    ) B

    on A.group=B.group and A.subgroup=B.subgroup

  • Hi

    I would use GROUP BY and MIN.

    DECLARE @t TABLE (GroupId INT, Subgroup INT)

    INSERT INTO @t

    SELECT 1, 11

    UNION ALL SELECT 1, 12

    UNION ALL SELECT 1, 13

    UNION ALL SELECT 2, 21

    UNION ALL SELECT 2, 22

    UNION ALL SELECT 2, 23

    UNION ALL SELECT 3, 31

    UNION ALL SELECT 3, 32

    SELECT

    GroupId,

    MIN(Subgroup)

    FROM @t

    GROUP BY GroupId

    Greets

    Flo

  • Use row_number(). You can order it by whatever you want, in a sub-query, then filter the way you want in the outer query.

    Like this:

    ;with CTE as

    (select GroupNumber, SubGroup,

    row_number() over (partition by GroupNumber order by SubGroup) as Row

    from dbo.MyTable)

    select GroupNumber, SubGroup

    from CTE

    where Row = 1;

    Min() would probaby be more efficient, but you already said you can't use that in this case, so this method will probably be your best bet.

    - 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

  • I thought about this, but problem is - I have more fields - and for every filds I must prepare subquery.

    In MSAcces there is group function FIRST and the solution may be

    select group, first(subgroup), ....

    from table

    group by group

    I don't know anything about such function in sqlserver

    Second solution is to make join, but it need join

    select t1.group, t2. subgroup

    from table t1 inner join table t2 on t1.group = t2.group

    order by t2.subgroup

    but in this solution I want to get join 1 -1 not 1 to many

  • I supposed wider problem - min() is not a good solution. In MsAccess there is group function FIRST()

    and I may write:

    select group, first(subgroup), ....

    from table

    group by group

    I dont know similar

    other solution is

    select t1.group, t2. subgroup

    from table t1 inner join table t2 on t1.group = t2.group

    order by t2.subgroup

    but how to get only one record from each pair ?

  • Hi

    If MIN is not the solution you are looking for you should take GSquared's solution with a CTE.

    Greets

    Flo

  • stoler (5/15/2009)


    I thought about this, but problem is - I have more fields - and for every filds I must prepare subquery.

    In MSAcces there is group function FIRST and the solution may be

    select group, first(subgroup), ....

    from table

    group by group

    I don't know anything about such function in sqlserver

    Second solution is to make join, but it need join

    select t1.group, t2. subgroup

    from table t1 inner join table t2 on t1.group = t2.group

    order by t2.subgroup

    but in this solution I want to get join 1 -1 not 1 to many

    There's no First() function in SQL because it's meaningless in a relational database. There's no inherent row-order in a relational table, so "first()" is useless.

    - 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

  • I'd like a First() function in SQL server, often I need to get a value from a row that has been replicated by a join to another table. I know that all the values in my group by are the same, so bringing back any of them is fine. It seems like doing a MAX() or grouping by this column as well is a waste of resources.

    Does that make sense?

  • GSquaredThere's no First() function in SQL because it's meaningless in a relational database. There's no inherent row-order in a relational table, so "first()" is useless.

    It's not meaningless with an OVER (ORDER BY) clause 😉

    ORACLE implements FIRST.

    Paul

  • waxingsatirical (3/8/2010)


    I'd like a First() function in SQL server, often I need to get a value from a row that has been replicated by a join to another table. I know that all the values in my group by are the same, so bringing back any of them is fine. It seems like doing a MAX() or grouping by this column as well is a waste of resources. Does that make sense?

    Yes it does. There are many alternatives to work around the lack of FIRST, however. All are more efficient than grouping using an arbitrary aggregate. The following script should give you some ideas:

    --

    -- "FIRST" equivalents

    --

    -- Test table

    DECLARE @T

    TABLE (

    group_id INTEGER NOT NULL,

    subgroup INTEGER NOT NULL,

    PRIMARY KEY (group_id, subgroup)

    );

    -- Sample data from Flo

    INSERT @T

    (group_id, subgroup)

    SELECT 1, 11 UNION ALL

    SELECT 1, 12 UNION ALL

    SELECT 1, 13 UNION ALL

    SELECT 2, 21 UNION ALL

    SELECT 2, 22 UNION ALL

    SELECT 2, 23 UNION ALL

    SELECT 3, 31 UNION ALL

    SELECT 3, 32;

    -- Solution by Flo

    SELECT group_id,

    MIN(subgroup)

    FROM @t

    GROUP BY

    group_id

    ORDER BY

    group_id;

    -- Solution by G2

    SELECT T1.group_id,

    T1.subgroup

    FROM (

    SELECT group_id,

    subgroup,

    rn = ROW_NUMBER()

    OVER (

    PARTITION BY group_id

    ORDER BY subgroup)

    FROM @T T1

    ) T1

    WHERE T1.rn = 1

    ORDER BY

    T1.group_id;

    -- "Segment Top" solution

    SELECT T1.group_id,

    T1.subgroup

    FROM @T T1

    WHERE T1.subgroup =

    (

    SELECT MIN(subgroup)

    FROM @T T2

    WHERE T2.group_id = T1.group_id

    )

    ORDER BY

    T1.group_id;

    -- APPLY TOP solution

    SELECT T1.group_id,

    iTVF.subgroup

    FROM (

    SELECT DISTINCT group_id

    FROM @T T

    ) T1

    CROSS

    APPLY (

    SELECT TOP (1)

    T2.subgroup

    FROM @T T2

    WHERE T2.group_id = T1.group_id

    ORDER BY

    T2.group_id

    ) iTVF

    ORDER BY

    T1.group_id;

    Paul

  • some timings... rough as I don't have a dedicated server to play with and only used one set of data.

    Solution by G2

    640

    "Segment Top" solution

    750

    Cross apply solution

    733

    Solution by Flo

    563

    Flo's solution was consistently the fastest, not dependant on order of queries. As you can see there's not much in it though.

  • It is very important not to generalise on performance here, since it is so dependent on data distribution (especially the number of groups, and average number of members per group). All are fine solutions.

    I should mention though, that Flo's MIN + GROUP BY is the only one that cannot return extra columns. Add an extra column to the test table to see what I mean. Horses for courses though.

    Paul

Viewing 13 posts - 1 through 12 (of 12 total)

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