SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


first element in sublist


first element in sublist

Author
Message
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16352 Visits: 11355
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
SQLblog.com
@SQL_Kiwi
waxingsatirical
waxingsatirical
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 241
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.
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16352 Visits: 11355
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search