SQLServer Order By IGNORE NULL values

Question

For some reason I can't change the table or update the data for now. Here the problem :

I have menu_user table below :

userID  menuID
(null)  2
(null)  3
1       3
2       1
3       2
4       5
5       0

userID and menuID not duplicated. The problem is how to ORDER BY userID, menuID but when userID has NULL value, it will look for another row that has same menuID and place it after this row. menuID just have max 2 same value and if it have, another one must be NULL

The order result expected :

userID  menuID
1       3
(null)  3
2       1
3       2
(null)  2
4       5
5       0

Here the script sample :

CREATE TABLE [dbo].[menu_user](
    [userID] [int] NULL,
    [menuID] [int] NULL
);

INSERT [dbo].[menu_user] ([userID], [menuID]) VALUES (NULL, 3);
INSERT [dbo].[menu_user] ([userID], [menuID]) VALUES (1, 3);
INSERT [dbo].[menu_user] ([userID], [menuID]) VALUES (2, 1);
INSERT [dbo].[menu_user] ([userID], [menuID]) VALUES (3, 2);
INSERT [dbo].[menu_user] ([userID], [menuID]) VALUES (4, 5);
INSERT [dbo].[menu_user] ([userID], [menuID]) VALUES (5, 0);
INSERT [dbo].[menu_user] ([userID], [menuID]) VALUES (NULL, 2);

ADDED If possible I want this script as View (just SELECT with No Variable).

You won't be able to have it as a view. Views (like tables) have no inherent order - since this question is all about order, that's not going to work. — Damien_The_Unbeliever, Aug 16 '17 at 06:02
@Damien_The_Unbeliever : I think you got it right, Thank you. — Jun Rikson, Aug 16 '17 at 06:05

score 1 · Accepted Answer · answered Aug 16 '17 at 06:02

This seems to do the trick. You need to do something to relate multiple rows together. Here I've chosen to use a left join:

select
    m1.*
from
    menu_user m1
        left join
    menu_user m2
        on
            m1.userID is null and
            m1.menuID = m2.menuID and
            m2.userID is not null
order by
    COALESCE(m1.userID,m2.userID),m1.userID desc

Result:

userID      menuID
----------- -----------
1           3
NULL        3
2           1
3           2
NULL        2
4           5
5           0

Hopefully you can see how it's achieving its aims.

Thank you very much, I got it fully understand by `select *`. And I'm sorry I didn't make full example, I modify the order to `COALESCE(m1.userID,m2.userID),m1.menuID,m1.userID desc`, I don't know why this is no work in sqlfiddle, but it is work really well in my sqlserver. Thank you http://sqlfiddle.com/#!6/64ed35/2 — Jun Rikson, Aug 16 '17 at 06:20

score 0 · Answer 2 · answered Aug 16 '17 at 04:05

0

Check this, ordering is bit messed up but this gives you your desired result.

SELECT * FROM menu_user mu
ORDER BY mu.menuID,
CASE WHEN mu.userID IS NULL THEN mu.menuID END

answered Aug 16 '17 at 04:05

Sandip Bantawa

2,822
4
31
47

it order by `menuID` at first, I need `userID` first but thank you for your answer. – Jun Rikson Aug 16 '17 at 04:13

score 0 · Answer 3 · answered Jun 20 '18 at 04:50

Using the left join solution will produce duplicates when there are more than 1 non-null Users for a menuID. This is another method.

select userID, menuID
From (
select *, Case when a.UseriD is not null then cast(a.userID as float) else
            (select max(b.userID) + 0.1 from menu_user b where a.menuID = b.menuID and a.userID is null) end as SortCol
    from menu_user a
) c Order by  SortCol

score -2 · Answer 4 · edited Aug 16 '17 at 06:24

-2

Try this query:

SELECT * 
FROM menu_user mu
WHERE userID IS NOT NULL
ORDER BY mu.menuID

edited Aug 16 '17 at 06:24

marc_s

732,580
175
1,330
1,459

answered Aug 16 '17 at 05:16

Santhosh Raja

39
1
9

Your query filters userID = null records. But Question clearly defines that null records also should be selected BUT ordered by unusual – ilkerkaran Aug 16 '17 at 05:30

SQLServer Order By IGNORE NULL values

4 Answers4