How to Group By and Order By - SQL Server

Question

I need to group by an UniqueIdentifier column and then sort the group by a DateTime 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:10: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:10: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 Sort the Column [dbo].[StudentMark].[GeneratedOn] within a group.

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]
ORDER BY 
    [GeneratedOn] DESC

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.

Expected Result Set:

3, '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'

2, '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:10:15'

4, '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'

1, '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:10:15'

I refereed the following question but I can't fix it: SQL Group with Order by

For your kind information I'm using SQL Server 2016.

Kindly assist me.

You typically GROUP BY the selected columns that are not arguments to a set function. In this case [StudentId], [SubjectId], [ScoreInfo], [GeneratedOn]. — jarlh, Aug 21 '17 at 10:56
@jarlh - I'm getting the following error `The XML data type cannot be compared or sorted, except when using the IS NULL operator.` — B.Balamanigandan, Aug 21 '17 at 10:58
Edit your question and provide sample data and sample results. A `GROUP BY` query produces one row per group. There is no sorting "within" a group. — Gordon Linoff, Aug 21 '17 at 10:58
@GordonLinoff - Sample seed data already I given, kindly wait 2 more minutes I will update the expected result. — B.Balamanigandan, Aug 21 '17 at 10:59

score 3 · Accepted Answer · edited Aug 21 '17 at 12:33

SELECT MAX([StudentMarkId]), 
    [StudentId], 
    [SubjectId], 
    convert(varchar(max),[ScoreInfo]) as [ScoreInfo] , [GeneratedOn]
FROM [dbo].[StudentMark] 
GROUP BY [StudentId], [SubjectId], convert(varchar(max), [ScoreInfo]), [GeneratedOn]
ORDER BY [GeneratedOn] DESC

Check this also

SELECT MAX([StudentMarkId]) 
        over (partition by [StudentId] order by [GeneratedOn] desc) as maxStudentMarkId, 
    [StudentId],
    [SubjectId],
    convert(varchar(max),[ScoreInfo]) as [ScoreInfo] , [GeneratedOn]
FROM [dbo].[StudentMark]

Output -

maxStudentMarkId    StudentId   SubjectId   ScoreInfo   GeneratedOn
4   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"/>  2017-08-16 09:06:20.00
4   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"/>  2017-08-10 10:10:15.00
3   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"/>  2017-08-16 09:06:20.00
3   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"/>  2017-08-10 10:10:15.00

Kindly check the resultant record for your query, its returning the recordset in the order `4, 3 , 2, 1` or `[StudentMarkId]` — B.Balamanigandan, Aug 21 '17 at 11:13
it is due to ORDER BY [GeneratedOn] DESC. can you share expected output? — Anagha, Aug 21 '17 at 11:20
The second query is working, my question is we can't achieve this by using `Group By` ? — B.Balamanigandan, Aug 21 '17 at 11:42
you want to use aggregate function on only one column so partition by gives better solution that is depending on your requirements.For more information check this - https://stackoverflow.com/questions/2404565/sql-server-difference-between-partition-by-and-group-by — Anagha, Aug 21 '17 at 11:52

How to Group By and Order By - SQL Server

1 Answers1