Most efficient way to get columns where one is minimum

Question

I have a table with 3 columns in the format below:

WebcastChannelId, WebcastEventId, SortSequence

• 1 , 2 , 1
• 1 , 5 , 2
• 1 , 3 , 3
• 2 , 7 , 2
• 2 , 8 , 1

I would like to get the values for the webcastChannelId and WebcastEventId with the minimum sort sequence.

In the above example this would be:

WebcastChannelId, WebcastEventId, SortSequence

• 1 , 2 , 1
• 2 , 8 , 1

I have the following, which gets what I require, but I don't know if there is a more efficient way to do this?

SELECT wcwel.WebcastChannelId, wcwel.WebcastEventId
FROM [dbo].[WebcastChannelWebcastEventLink] wcwel with (nolock)
inner join [dbo].[WebcastChannelWebcastEventLink] wcwel1 with (nolock) on wcwel1.WebcastChannelId = wcwel.WebcastChannelId
group by
wcwel.WebcastChannelId,
wcwel.WebcastEventId,
wcwel.SortSequence
having
wcwel.SortSequence = MIN(wcwel1.SortSequence)

Answer 1

Formatting

You use a mixture of lowercase and UPPERCASE keywords, you should stick to one or the other. It makes it much easier to read the query if you do. I am going to stick with UPPERCASE as it is the convention.

An alternate approach

Might I suggest storing the minimum SortSequence value in a variable and then using that to filter out records you don't need.

Here is what I came up with.

DECLARE @Minimum_Sort_Seq INT = 
(
    SELECT MIN(SortSequence)
    FROM WebcastChannelWebcastEventLink
)

SELECT wcwel.WebcastChannelId,wcwel.WebcastEventId
FROM WebcastChannelWebcastEventLink wcwel
WHERE wcwel.SortSequence = @Minimum_Sort_Seq

This query returned the same results in my testing. You can run this to check yourself:

CREATE TABLE #WebcastChannelWebcastEventLink
(
    WebcastChannelId INT,
    WebcastEventId INT,
    SortSequence INT
) 

INSERT INTO #WebcastChannelWebcastEventLink
SELECT 1,2,1 UNION ALL
SELECT 1,5,2 UNION ALL
SELECT 1,3,3 UNION ALL
SELECT 2,7,2 UNION ALL
SELECT 2,8,1

SELECT wcwel.WebcastChannelId, wcwel.WebcastEventId
FROM #WebcastChannelWebcastEventLink wcwel WITH (NOLOCK)
INNER JOIN #WebcastChannelWebcastEventLink wcwel1 WITH (NOLOCK) 
    ON wcwel1.WebcastChannelId = wcwel.WebcastChannelId
GROUP BY
wcwel.WebcastChannelId,
wcwel.WebcastEventId,
wcwel.SortSequence
HAVING
wcwel.SortSequence = MIN(wcwel1.SortSequence)

DECLARE @Minimum_Sort_Seq INT = 
(
    SELECT MIN(SortSequence)
    FROM WebcastChannelWebcastEventLink
)

SELECT wcwel.WebcastChannelId,wcwel.WebcastEventId
FROM WebcastChannelWebcastEventLink wcwel
WHERE wcwel.SortSequence = @Minimum_Sort_Seq

DROP TABLE #WebcastChannelWebcastEventLink