thomsan
thomsan

Reputation: 483

Can't order by name when use Group By

I'm using following SQL query to insert data to temp table

INSERT INTO @tmp_Notify 
    SELECT     'U' + CONVERT(varchar,UserId), CONVERT([varchar](50), [FirstName]) + ' ' + CONVERT([varchar](50), [LastName])
    FROM  [User] 
            INNER JOIN dbo.UserGroup ON UserId = FkUserId  
            INNER JOIN [Group] ON GroupId = FkGroupId 
    GROUP BY UserId, CONVERT([varchar](50), [FirstName]), CONVERT([varchar](50), [LastName])
    HAVING MIN(GroupLevel) >= @minGroup
    ORDER BY CONVERT([varchar](50), [FirstName]), CONVERT([varchar](50), [LastName])

I'm inserting several data as follows,

Inserting group data as

INSERT INTO @tmp_Notify 
    SELECT     'G' + CONVERT(varchar,GroupId), 'Group - ' + GroupName
    FROM  [Group] 
    WHERE GroupLevel >= @minGroup AND IsActive = 1
    ORDER BY GroupName

So I need to display user name list first then group names order by GroupName.

My full code is

    --Users of the same group as currently logged in user and userÆs of higher groups
    INSERT INTO @tmp_Notify 
        SELECT     'U' + CONVERT(varchar,UserId), CONVERT([varchar](50), DECRYPTBYKEY([FirstName])) + ' ' + CONVERT([varchar](50), DECRYPTBYKEY([LastName]))
        FROM  [User] 
                INNER JOIN dbo.UserGroup ON UserId = FkUserId  
                INNER JOIN [Group] ON GroupId = FkGroupId 
        GROUP BY UserId, CONVERT([varchar](50), DECRYPTBYKEY([FirstName])), CONVERT([varchar](50), DECRYPTBYKEY([LastName]))
        HAVING MIN(GroupLevel) >= @minGroup
        ORDER BY CONVERT([varchar](50), DECRYPTBYKEY([FirstName])), CONVERT([varchar](50), DECRYPTBYKEY([LastName]))

    select * from @tmp_Notify

    --Currently logged in userÆs group and higher groups
    INSERT INTO @tmp_Notify 
        SELECT     'G' + CONVERT(varchar,GroupId), 'Group - ' + GroupName
        FROM  [Group] 
        WHERE GroupLevel >= @minGroup AND IsActive = 1
--      GROUP BY GroupId, GroupName
--      HAVING MIN(GroupLevel) >= @minGroup
        ORDER BY GroupName
END
ELSE
BEGIN
    INSERT INTO @tmp_Notify VALUES('Me','Me')

    IF((SELECT FkSupervisor FROM [User] WHERE UserId = @currentUser) IS NOT NULL)
    BEGIN
        INSERT INTO @tmp_Notify VALUES('Supervisor','Supervisor')
    END
END

    --List Option (to allow a list of email addresses)
    INSERT INTO @tmp_Notify VALUES('List','List')   

    --SELECT * FROM @tmp_Notify tn order by tn.NotifyName
    SELECT * FROM @tmp_Notify

But its not order to correct way, I need to sort data by FirstName and LastName. How can I do it

My expected output is

U10059  Emily Windrow
U10039  Esta Kulzer
U10030  Ileana Conklin
U10040  Kamala Millerbernd
U10007  Katherine Tayan
G110    Group - Managers
G112    Group - Membership Officers
G113    Group - Mortgage
G108    Group - Operations Administrator
G109    Group - PFS
List    List

Upvotes: 0

Views: 77

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15150

You can get what you want using something similar to this:

DECLARE @tmp_Notify TABLE (id INT IDENTITY(1, 1), FirstName VARCHAR(50), lastname VARCHAR(50))

INSERT @tmp_Notify (FirstName, lastname)
VALUES ( 'Bob', 'Curtis')
,       ('Mary', 'Magdalene')

INSERT @tmp_Notify (FirstName, lastname)
VALUES ( 'GROUP', '1')
,       ('GROUP', '2')

INSERT @tmp_Notify (FirstName, lastname)
VALUES ( 'List', 'List')

SELECT  TN.id
,       TN.FirstName
,       TN.lastname
FROM    @tmp_Notify AS TN
ORDER BY TN.id

In this case you can use an ORDER BY in your INSERT, because that will determine the value of id.

Upvotes: 1

BarneyL
BarneyL

Reputation: 1362

This is a common misconception.

Adding an order to an insert statement has no meaning because a raw table of data has no inherent guaranteed order to it. Within SQL Server regardless of the order the data goes in it will be stored within the clustered index and thus ordered by that. It will also be added to any other indexes in the table which might be different again. If you have no indexes at all and your table is a heap then the order will have no certainty to it whatever you do.

When you come to query a table there is no default guaranteed order the data will be returned in unless you specify it with ORDER BY. Without this the returned data can in theory be in any order (SQL Server will output in whatever order allows it to complete the query in the fastest time).

The order you put data in to a table has no bearing on the order you will get it back out again.

Upvotes: 2

Related Questions