Click here to Skip to main content
13,904,127 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

8.4K views
78 downloads
8 bookmarked
Posted 18 Feb 2019
Licenced CPOL

SQL Server: Select Single Row of Each Group/GroupBy

, 18 Feb 2019
Rate this:
Please Sign up or sign in to vote.
Selecting single row from a group of rows

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:

Nth Row

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

Specific Nth 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.

Good to Read

Please find the necessary SQL file as an attachment.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

DiponRoy
Bangladesh Bangladesh
No Biography provided

You may also be interested in...

Pro

Comments and Discussions

 
SuggestionRanking Pin
tassadaque18-Feb-19 20:56
membertassadaque18-Feb-19 20:56 
GeneralRe: Ranking Pin
DiponRoy20-Feb-19 7:35
memberDiponRoy20-Feb-19 7:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02 | 2.8.190306.1 | Last Updated 18 Feb 2019
Article Copyright 2019 by DiponRoy
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid