1. Beatrix Willius
  2. Valentina Database ADK
  3. Понедельник, Март 02 2020, 03:02 PM
  4.  Подписаться через email
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?
Вложения
Комментарий
There are no comments made yet.
Beatrix Willius Ответ принят
Thanks.

I didn't even come to the number of columns because my first test crashed. I'll fix the columns with 10.0.2.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
This issue was fixed in v.10.0.2.
But yo do something like this one


select f1 from t1
UNION
select f1, f2 from t1


Sure, it should not be crashed but error (in v.10.0.2) because number of columns are different.
Комментарий
There are no comments made yet.
Beatrix Willius Ответ принят
By the way, I had to do a second union and that makes a nice crash in VStudio.
Комментарий
There are no comments made yet.
Ivan Smahin Ответ принят
Also, if you are sure (or you don't care about it) that both part of UNION contain unique data you can do UNION ALL. In such case we do no sorting inside to filter out duplicates and just sew them together. It is most speedy UNION case.
Комментарий
There are no comments made yet.
Beatrix Willius Ответ принят
Thanks! I thought I had tried that.

With a bit trickery I got the query I need:


(select RecID, MailboxPath as FinalPath, WhereClause, PathSort(MailboxPathNatSort), '' as usernamefinal from mailbox where username = 'sa')
union
(select recid, UsernameAndMailboxPath as FinalPath, WhereClause, PathSort(MailboxPathNatSort), username as usernamefinal from mailbox where username in ('testuser', 'default user'))
order by usernamefinal, 4
Комментарий
There are no comments made yet.
François Van Lerberghe Ответ принят
Hi Beatrix,

I think you must add the ORDER BY clause at the end of the union :

(Query1)
UNION
(Query2)
ORDER BY 2 // if you want order by the 2d field of the union
Комментарий
There are no comments made yet.
  • Страница :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.