Does Order By no work on a Union - it does not give me any error in ElevateDB Manager - and it does not sort - no matter which field I try to sort on.....

Is this a known issue?


select F.Fid, F.MainLastName, F.MainFirstName + ' ' + F.MainLastName as ParentName, F.MainEmail as Email
From Family F
 Inner Join Order ORD On ORD.Fid = F.Fid
 Inner Join Session SES on SES.SessID = ORD.SessID

Where ORD.Status = 'Paid'
 and SES.OpenTF = True
                                                
union

select F.FId,  F.SecLastName, F.SecFirstName + ' ' + F.SecLastName, F.SecEmail
From Family F
 Inner Join Order ORD On ORD.Fid = F.Fid
 Inner Join Session SES on SES.SessID = ORD.SessID

Where ORD.Status = 'Paid'
 and SES.OpenTF = True
 and F.SecEmail > ''

Order by F.Fid desc


Walter

Walter,

I believe you want to use UNION ALL instead of UNION, but I'm not sure what you want as a result.

Also, for sorting to work, the name of the columns you use to sort have to be the same in both selects,
so for FId it's fine but for the other columns you have to rename them, for example, use
"F.MainLastName AS LastName" in first select and "F.SecLastName AS LastName" in the second one if you want to sort by that column.

--
Fernando Dias
[Team Elevate]

Às 20:18 de 26/02/2025, Walter Matte escreveu:
> Does Order By no work on a Union - it does not give me any error in ElevateDB Manager - and it does not sort - no matter which field I try to sort on.....
>
> Is this a known issue?
>
>
> select F.Fid, F.MainLastName, F.MainFirstName + ' ' + F.MainLastName as ParentName, F.MainEmail as Email
>  From Family F
>    Inner Join Order ORD On ORD.Fid = F.Fid
>    Inner Join Session SES on SES.SessID = ORD.SessID
>
> Where ORD.Status = 'Paid'
>    and SES.OpenTF = True
>                                                   
> union
>
> select F.FId,  F.SecLastName, F.SecFirstName + ' ' + F.SecLastName, F.SecEmail
>  From Family F
>    Inner Join Order ORD On ORD.Fid = F.Fid
>    Inner Join Session SES on SES.SessID = ORD.SessID
>
> Where ORD.Status = 'Paid'
>    and SES.OpenTF = True
>    and F.SecEmail > ''
>
> Order by F.Fid desc
>
>
> Walter
>

Fernando Dias wrote:

UNION ALL - is not what I want - it duplicates data

and

Using same field names does NOT work - throws an obfuscated error message




select F.Fid, F.MainLastName as LN, 'Primary' as ContactType,  F.MainFirstName + ' ' + F.MainLastName as ParentName, F.MainEmail as Email
From Family F
 Inner Join Order ORD On ORD.Fid = F.Fid
 Inner Join Session SES on SES.SessID = ORD.SessID

Where ORD.Status = 'Paid'
 and SES.OpenTF = True
                                                                                                           
union

select F.FId,  F.SecLastName as LN, 'Second' as ContactType, F.SecFirstName + ' ' + F.SecLastName, F.SecEmail             
From Family F
 Inner Join Order ORD On ORD.Fid = F.Fid
 Inner Join Session SES on SES.SessID = ORD.SessID

Where ORD.Status = 'Paid'                                                                                
 and SES.OpenTF = True                                   
 and F.SecEmail > ''

Order by LN

Fails

I found an old thread that lead me to a subselect ... and a work around....

https://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&msg=7911

Walter

Walter

Whilst I have no idea just what it is you're trying to achieve here I think the following will work.



SELECT * FROM
(select Fid, MainLastName, MainFirstName + ' ' + MainLastName as ParentName, MainEmail as Email
From Family
 Inner Join Order ORD On ORD.Fid = Fid
 Inner Join Session SES on SES.SessID = ORD.SessID

Where ORD.Status = 'Paid'
 and SES.OpenTF = True
                                               
union

select FId,  SecLastName, SecFirstName + ' ' + SecLastName, SecEmail
From Family
 Inner Join Order ORD On ORD.Fid = Fid
 Inner Join Session SES on SES.SessID = ORD.SessID

Where ORD.Status = 'Paid'
 and SES.OpenTF = True
 and SecEmail > ''
) X
Order by Fid desc

I doubt that the SQL interpreter has any idea which of the two F.Fld it should be pointing at, if any. It may be the second but it may be neither (which would be my guess) since internally applying to one part of the UNION an ORDER BY make no sense. Externally the UNION is producing a new result set which is not referenced by your alias F



Roy Lambert