B.Balamanigandan B.Balamanigandan - 1 month ago 6
SQL Question

How to Group By a column - SQL Server

I need to group by an

UniqueIdentifier
column, the table also contains the XML column.

Table schema: StudentMark:

CREATE TABLE [dbo].[StudentMark]
(
[StudentMarkId] [int] IDENTITY(1,1) NOT NULL,
[StudentId] [uniqueidentifier] NULL,
[SubjectId] [uniqueidentifier] NULL,
[ScoreInfo] [xml] NULL,
[GeneratedOn] [datetime2](2) NOT NULL,

CONSTRAINT [PK_StudentMark]
PRIMARY KEY CLUSTERED ([StudentMarkId] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


Sample seed data

INSERT INTO [dbo].[StudentMark] ([StudentId], [SubjectId], [ScoreInfo], GeneratedOn])
VALUES ('FC3CB475-B480-4129-9190-6DE880E2D581', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-10 10:20:15'),
('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-10 10:20:15'),
('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-16 09:06:20'),
('FC3CB475-B480-4129-9190-6DE880E2D581', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-16 09:06:20');


Requirement: I need to group by
[dbo].[StudentMark].[StudentId]
and take the latest record.

I tried the following SQL query but it is causing an error

SELECT
MAX([StudentMarkId]), [StudentId], [SubjectId], [ScoreInfo], [GeneratedOn]
FROM
[dbo].[StudentMark]
GROUP BY
[StudentId]


Error:


Column 'dbo.StudentMark.SubjectId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


I refereed the following question but I can't fix it: Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Kindly assist me.

Answer Source

Use ROW_NUMBER to calculate position within group:

SELECT *
FROM (
    SELECT *,
      ROW_NUMBER() OVER(PARTITION BY StudentId ORDER BY StudentMarkId DESC) AS rn
    FROM [dbo].[StudentMark]) sub
WHERE sub.rn = 1;