I'm trying to do a union but I can't get the order correct.
The table is simple: Mailboxpath, Username are the main fields. The table has a custom sort so that the records sort like
inbox
sent messages
other mailboxes
trash
I'm showing the data for the main user without the user name. Data for other users should show the user name.
The first query for the current user works fine:
select RecID, MailboxPath as FinalPath, WhereClause, PathSort(MailboxPathNatSort) from mailbox where username = 'sa' order by 4
RecID FinalPath WhereClause No_name4
2 Import-1 6:Import-00001
3 Import-1:::mails for parser sing 6:Import-00001mails for parser sing
1 Trash 7:Trash
The second is for data of other users and also is okay:
select recid, UsernameAndMailboxPath as FinalPath, WhereClause, PathSort(MailboxPathNatSort) from mailbox where username in ('testuser', 'default user') order by username, 4
RecID FinalPath WhereClause No_name4
12 default user:::Inbox 1:Inbox
13 default user:::Inbox:::Account 1 1:InboxAccount 1
15 default user:::Sent Messages 2:Sent Messages
14 default user:::Trash 7:Trash
6 testuser:::Inbox 1:Inbox
7 testuser:::Inbox:::Account 1 1:InboxAccount 1
9 testuser:::Sent Messages 2:Sent Messagesmails for parser sing
8 testuser:::Trash 7:Trash
When I unionise the 2 queries the sort order isn't okay:
(select RecID, MailboxPath as FinalPath, WhereClause, PathSort(MailboxPathNatSort) from mailbox where username = 'sa' order by 4)
union
(select recid, UsernameAndMailboxPath as FinalPath, WhereClause, PathSort(MailboxPathNatSort) from mailbox where username in ('testuser', 'default user') order by username, 4)
The result is in the screenshot.
What am I doing wrong?