Reputation: 483
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
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
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