1. Beatrix Willius
  2. Valentina Database ADK
  3. Montag, März 02 2020, 03:02 PM
  4.  Abonnieren via E-Mail
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?
Anhänge
Kommentar
There are no comments made yet.
François Van Lerberghe Akzeptierte Antwort
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
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
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
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
By the way, I had to do a second union and that makes a nice crash in VStudio.
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
  • Seite :
  • 1


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

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories