May 14, 2009 at 1:09 pm
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
May 14, 2009 at 1:35 pm
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
May 14, 2009 at 2:04 pm
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
May 14, 2009 at 2:21 pm
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
May 15, 2009 at 12:04 am
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
May 15, 2009 at 12:10 am
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 ?
May 15, 2009 at 2:41 am
Hi
If MIN is not the solution you are looking for you should take GSquared's solution with a CTE.
Greets
Flo
May 15, 2009 at 7:17 am
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
March 8, 2010 at 2:13 am
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?
March 8, 2010 at 5:57 am
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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 8, 2010 at 6:11 am
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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 8, 2010 at 6:37 am
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.
March 8, 2010 at 6:48 am
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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply