Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

first element in sublist Expand / Collapse
Author
Message
Posted Monday, March 8, 2010 6:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 11,192, Visits: 11,098
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #878542
Posted Monday, March 8, 2010 6:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:24 AM
Points: 58, Visits: 188
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.
Post #878557
Posted Monday, March 8, 2010 6:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 11,192, Visits: 11,098
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #878564
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse