SQL Server: Select Single Row From Each Group





4.00/5 (3 votes)
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.
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
Please find the necessary SQL file as an attachment.