Reputation: 17859
I have this SQL:
SELECT
--CASE WHEN EA.[EntityAttentionTypeId] IS NULL THEN 1000+(ROW_NUMBER() OVER (ORDER BY EA.[EntityAttentionTypeId] )) ELSE ROW_NUMBER() OVER (ORDER BY EA.[EntityAttentionTypeId] ) END AS C_NO,
ROW_NUMBER() OVER (ORDER BY EA.[EntityAttentionTypeId]) AS C_NO,
EA.[EntityAttentionTypeId],
C.PreName AS C_TITLE,
C.FirstName AS C_NAME,
C.LastName AS C_SURNAME,
C.Email AS C_EMAIL,
CASE WHEN EA.[EntityAttentionTypeId] = 1 THEN 'MAIN CONTACT' ELSE '' END AS C_COMMENTS,
CASE WHEN EA.[EntityAttentionTypeId] = 1 THEN 'v' ELSE ' ' END AS C_INV,
CASE WHEN EA.[EntityAttentionTypeId] = 1 THEN 'v' ELSE ' ' END AS C_UPDATES,
CASE WHEN EA.[EntityAttentionTypeId] = 1 THEN 'v' ELSE ' ' END AS C_DOWN
FROM
[CMSTIME].[dbo].[Contacts] C
LEFT OUTER JOIN
[CMSTIME].[dbo].[Entities] E ON E.EntityId = C.EntityId
FULL OUTER JOIN
[CMSTIME].[dbo].[EntityAttentions] EA ON EA.[PeopleId]= C.[ContactId]
WHERE
C.ContactId IN (SELECT Entity2People.EmployeeId
FROM Entity2People
WHERE Entity2People.EntityId = 307)
ORDER BY
C_NO
I get this result:
How can I change my SQL so that I get rows with nulls EntityAttentionTypeId at the bottom and the C_NO still to read 1,2,3,4...etc
I've tried
CASE WHEN EA.[EntityAttentionTypeId] IS NULL THEN 1000+(ROW_NUMBER() OVER (ORDER BY EA.[EntityAttentionTypeId] )) ELSE ROW_NUMBER() OVER (ORDER BY EA.[EntityAttentionTypeId] ) END AS C_NO,
but i get something like:
Thank you all in advance
Upvotes: 0
Views: 37
Reputation: 69829
I think you want ORDER BY -EntityAttentionTypeId DESC
within the ROW_NUMBER()
function
Since ASC
will put NULL
first, then order by non null values, if you order by DESC
it will put NULL
last. This of course would reverse the required order of Non Null values, so if you use the -
operator you get back to the correct order, while still keeping NULL
at the end.
e.g.
SELECT ROW_NUMBER() OVER (ORDER BY -EntityAttentionTypeId DESC) AS C_NO,
EntityAttentionTypeId
FROM (VALUES (NULL), (1), (2), (3)) t (EntityAttentionTypeId)
ORDER BY C_NO;
Gives:
C_NO EntityAttentionTypeId
-----------------------------
1 1
2 2
3 3
4 NULL
Upvotes: 2
Reputation: 1271231
Hmmm, I think this resulting query should redefine C_NO
using both ROW_NUMBER()
and a CASE
:
SELECT (ROW_NUMBER() OVER (ORDER BY EA.[EntityAttentionTypeId]) +
(CASE WHEN EA.EntityAttentionTypeId] IS NULL THEN 1000 ELSE 0 END)
) AS C_NO,
. . .
ORDER BY C_NO;
Upvotes: 0