SQL Server: Select Single Row From Each Group

• Download source files - 1.2 KB

Introduction

It is really great to use GROUP BY. But what about when we have to create a query that would:

• Select specific row/rows
• Combine multiple rows into a single row/column

of a group.

Check out the example below to walk through the code samples and final solutions to select a single row or to roll-up multiple rows into a single row in SQL Server.

Data

Think about a survey scenario, where a user has to answer a number of multiple choice questions. Plus the user can answer the same question as many times as he/she wants.

/*data*/
CREATE TABLE #tblSurvey(
    ParticipantName VARCHAR(50),
    QuestionName VARCHAR(50),
    SelectedOption VARCHAR(50),
    ResponseDateTime DATETIME
);
INSERT 
    INTO #tblSurvey 
    VALUES
    ('Dan', 'Q1', 'F1', '20180521 10:34:01 AM'),    /*20180521, 21May2018*/    
    ('Dan', 'Q1', 'F2', '20180521 10:34:03 AM'),    /*most*/
    ('Dan', 'Q1', 'F2', '20180521 10:34:05 AM'),    /*last by response time*/
    ('Dan', 'Q2', 'F1', '20180521 10:34:01 AM'),        
    ('Dan', 'Q2', 'F1', '20180521 10:34:03 AM'),    /*most*/    
    ('Dan', 'Q2', 'F2', '20180521 10:34:05 AM'),    /*last by response time*/
    ('Dan', 'Q3', 'F2', '20180521 10:34:01 AM'),        
    ('Dan', 'Q3', 'F2', '20180521 10:34:03 AM'),    /*most*/    
    ('Dan', 'Q3', 'F1', '20180521 10:34:05 AM');    /*last by response time*//*make all counts equal*/

/*make all counts equal*/
--INSERT 
--    INTO #tblSurvey 
--    VALUES
--    ('Dan', 'Q1', 'F1', '20180521 10:34:01 AM'),    /*20180521, 21May2018*/    
--    ('Dan', 'Q2', 'F2', '20180521 10:34:05 AM'),    /*last by response time*/
--    ('Dan', 'Q3', 'F1', '20180521 10:34:05 AM');    /*last by response time*/

SELECT * FROM #tblSurvey;

Regular Query With Where, Group, Having And Order

Here is a simple example to use WHERE, GROUP BY, HAVING and ORDER BY in a single query:

/*GROUP BY ParticipantName, QuestionName*/  
SELECT ParticipantName, QuestionName, COUNT(SelectedOption) AS OptionCount
FROM #tblSurvey
WHERE QuestionName IN ('Q1', 'Q2', 'Q3')
GROUP BY ParticipantName, QuestionName
HAVING COUNT(SelectedOption) > 1
ORDER BY QuestionName DESC;

Group Wise Last/First Row

GROUP BY ParticipantName, QuestionName wise first/last Inserted row comparing ResponseDateTime.

WITH LastResponse
AS
(
    SELECT ROW_NUMBER() OVER(
        PARTITION BY ParticipantName, QuestionName    /*group by*/
        ORDER BY ResponseDateTime DESC                /*for first try 'ASC'*/
    ) AS OrderId, *
    FROM #tblSurvey
)
SELECT *
    FROM LastResponse
    WHERE OrderId = 1;    /*ordered after PARTITION in a way, 
                            so that the targeted row will be at the top*/

We can add few more variations to this query, like:

N^th Row

WHERE OrderId = 3;        /*3th row*/

Specific N^th Rows

WHERE OrderId IN (3, 5);  /*specific Nth rows*/

N number of Rows

WHERE OrderId < 5;        /*4 number of rows*/

Group Wise Highest/Lowest Count Row

GROUP BY ParticipantName, QuestionName, SelectedOption wise most/least SelectedOption.

WITH OptionWiseCount
AS
(
    SELECT 
        *,
        COUNT(SelectedOption) OVER(PARTITION BY ParticipantName, QuestionName, SelectedOption) _
             AS OptionCount
    FROM #tblSurvey

    /*alternatively, we can use group by*/
    --SELECT ParticipantName, QuestionName, SelectedOption, COUNT(SelectedOption) AS OptionCount
    --FROM #tblSurvey
    --GROUP BY ParticipantName, QuestionName, SelectedOption
),
MostResponse
AS
(
    SELECT ROW_NUMBER() OVER(
        PARTITION BY ParticipantName, QuestionName       /*group by*/
        ORDER BY OptionCount DESC                        /*for lowest try 'ASC'*/
    ) AS OrderId, *
    FROM OptionWiseCount
)
SELECT *
    FROM MostResponse
    WHERE OrderId = 1; /*ordered after PARTITION in a way, 
                         so that the targeted row will be at the top*/

Group Rows to a Single Column

GROUP BY ParticipantName, QuestionName wise SelectedOption's to a single column:

DECLARE @separator CHAR = ',';
SELECT 
    ParticipantName, 
    QuestionName,
    COUNT(SelectedOption) AS TotalSelectedOption,
    STUFF((SELECT @separator + CONVERT(NVARCHAR(MAX), SelectedOption) 
            FROM #tblSurvey AS uc
            WHERE uc.ParticipantName = g.ParticipantName    /*group bys as equal*/
            AND uc.QuestionName = g.QuestionName
            ORDER BY SelectedOption
            FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)')
            , 1, 1, '') AS SelectedOptions
FROM #tblSurvey AS g
GROUP BY ParticipantName, QuestionName; 

This is also possible with a custom aggregate function.

Show Group Id

LastResponse
AS
(
    SELECT 
    ROW_NUMBER() OVER(
        PARTITION BY ParticipantName, QuestionName    /*group by*/
        ORDER BY ResponseDateTime DESC                /*for first try 'ASC'*/
    ) AS OrderId, 
    DENSE_RANK () OVER(
        ORDER BY ParticipantName, QuestionName          /*group by*/
    ) AS GroupId, 
    *
    FROM #tblSurvey
)
SELECT *
    FROM LastResponse; 

Good to Read

• https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Please find the necessary SQL file as an attachment.