InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 02:14:04 GMT20http://www.sqlservercentral.com/Forums/Topic717306-338-1.aspxIt 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.PaulMon, 08 Mar 2010 06:48:28 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspxsome timings... rough as I don't have a dedicated server to play with and only used one set of data.Solution by G2640"Segment Top" solution750Cross apply solution733Solution by Flo563Flo's solution was consistently the fastest, not dependant on order of queries. As you can see there's not much in it though.Mon, 08 Mar 2010 06:37:44 GMTwaxingsatiricalhttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspx[quote][b]waxingsatirical (3/8/2010)[/b][hr]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?[/quote]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:[code="sql"]---- "FIRST" equivalents---- Test tableDECLARE @T TABLE ( group_id INTEGER NOT NULL, subgroup INTEGER NOT NULL, PRIMARY KEY (group_id, subgroup) );-- Sample data from FloINSERT @T (group_id, subgroup)SELECT 1, 11 UNION ALLSELECT 1, 12 UNION ALLSELECT 1, 13 UNION ALLSELECT 2, 21 UNION ALLSELECT 2, 22 UNION ALLSELECT 2, 23 UNION ALLSELECT 3, 31 UNION ALLSELECT 3, 32;-- Solution by FloSELECT group_id, MIN(subgroup)FROM @tGROUP BY group_idORDER BY group_id; -- Solution by G2SELECT T1.group_id, T1.subgroupFROM ( SELECT group_id, subgroup, rn = ROW_NUMBER() OVER ( PARTITION BY group_id ORDER BY subgroup) FROM @T T1 ) T1WHERE T1.rn = 1ORDER BY T1.group_id;-- "Segment Top" solutionSELECT T1.group_id, T1.subgroupFROM @T T1WHERE T1.subgroup = ( SELECT MIN(subgroup) FROM @T T2 WHERE T2.group_id = T1.group_id )ORDER BY T1.group_id;-- APPLY TOP solutionSELECT T1.group_id, iTVF.subgroupFROM ( SELECT DISTINCT group_id FROM @T T ) T1CROSSAPPLY ( SELECT TOP (1) T2.subgroup FROM @T T2 WHERE T2.group_id = T1.group_id ORDER BY T2.group_id ) iTVFORDER BY T1.group_id;[/code]PaulMon, 08 Mar 2010 06:11:37 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspx[quote][b]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.[/quote]It's not meaningless with an OVER (ORDER BY) clause ;-)ORACLE implements FIRST.PaulMon, 08 Mar 2010 05:57:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspxI'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?Mon, 08 Mar 2010 02:13:38 GMTwaxingsatiricalhttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspx[quote][b]stoler (5/15/2009)[/b][hr]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 beselect group, first(subgroup), ....from tablegroup by groupI don't know anything about such function in sqlserverSecond solution is to make join, but it need join select t1.group, t2. subgroupfrom table t1 inner join table t2 on t1.group = t2.grouporder by t2.subgroup but in this solution I want to get join 1 -1 not 1 to many[/quote]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.Fri, 15 May 2009 07:17:48 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspxHiIf MIN is not the solution you are looking for you should take GSquared's solution with a CTE.GreetsFloFri, 15 May 2009 02:41:50 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspxI 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 tablegroup by groupI dont know similarother solution is select t1.group, t2. subgroupfrom table t1 inner join table t2 on t1.group = t2.grouporder by t2.subgroupbut how to get only one record from each pair ?Fri, 15 May 2009 00:10:11 GMTstolerhttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspxI 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 beselect group, first(subgroup), ....from tablegroup by groupI don't know anything about such function in sqlserverSecond solution is to make join, but it need join select t1.group, t2. subgroupfrom table t1 inner join table t2 on t1.group = t2.grouporder by t2.subgroup but in this solution I want to get join 1 -1 not 1 to manyFri, 15 May 2009 00:04:21 GMTstolerhttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspxUse 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:[code];with CTE as (select GroupNumber, SubGroup, row_number() over (partition by GroupNumber order by SubGroup) as Row from dbo.MyTable)select GroupNumber, SubGroupfrom CTEwhere Row = 1;[/code]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.Thu, 14 May 2009 14:21:21 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspxHiI would use GROUP BY and MIN.[code]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, 32SELECT GroupId, MIN(Subgroup) FROM @t GROUP BY GroupId[/code]GreetsFloThu, 14 May 2009 14:04:43 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspxselect A.* from table Ainner join (select distinctgroup,(select top 1 subgroup from table t where table.group=t.group order by subgroup) as subgroupfrom table as A) B on A.group=B.group and A.subgroup=B.subgroupThu, 14 May 2009 13:35:16 GMTFelixGhttp://www.sqlservercentral.com/Forums/Topic717306-338-1.aspxI have a problem. I have table:Group Subgroup1 111 121 13.....2 212 222 23......3 313 32......How to write sql statement that choose first element from every group ?1 112 213 31Don't use MIN(Subgroup) because in real solution I must use different order than subgroupI may use:select distinct group, (select top 1 subgroup from table t where table.group=t.group order by subgroup) as subgroup from tablebut it isn't usable if I want to list more fields. In MSAccess there is FIRST function witch is useful in itselect group, first(subgroup) from table group by groupThu, 14 May 2009 13:09:49 GMTstoler