Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 14 total |
UNION's ORDER BY |
Fri, Sep 19 2008 7:54 PM | Permanent Link |
"David Cornelius" | I have the following UNION statement, which works fine.
SELECT CustCompany AS CustName, Total FROM Orders UNION SELECT CompanyName AS CustName, Total FROM Invoices GROUP BY CustName But the resulting data set is not sorted by CustName. All the like CustNames are together, but not alphabetically. So I should be able to add an ORDER BY clause at the end of it all. ORDER BY CustName This, however, does NOT work. I get error 401, CustName does not exist. So I tried changing the ORDER BY clause to use the first statement's "CustName" field, which worked. ORDER BY CustCompany However using CompanyName, the second statement's "CustName" field, did not. Is this by design? I thought I could use CustName here? I did look through the SQL reference for this, but ended up just stumbling on this solution. -- David Cornelius CorneliusConcepts.com |
Sat, Sep 20 2008 8:22 AM | Permanent Link |
Fernando Dias Team Elevate | David,
> SELECT CustCompany AS CustName, Total > FROM Orders > UNION > SELECT CompanyName AS CustName, Total > FROM Invoices > GROUP BY CustName The GROUP BY in this statement affects only the second query (FROM Invoices), not the result of the union. On the contrary, the ORDER BY clause applies to the final result of the UNION operation, so ORDER BY "CustCompany" will order the entire result set by the first column of the entire resulting table. The reason why you can use "CustCompany" and not "CompanyName" is because the column names in the resulting table of a UNION are the columns names of the first table in the UNION. So it seems everything is working as expected. There is however a detail I can't answer (because I don't know the answer , that is why can't "CustName" be used instead of "CustCompany" in the ORDER BY. Also, if what you want is not only to sort but also to group the entire result table, then you can do that as follows: SELECT CustName, Total FROM( SELECT CustCompany AS CustName, Total FROM Orders UNION SELECT CompanyName AS CustName, Total FROM Invoices ) as TempTbl GROUP BY CustName -- Fernando Dias [Team Elevate] |
Sat, Sep 20 2008 12:23 PM | Permanent Link |
"Malcolm" | Fernando Dias wrote:
> David, > > > SELECT CustCompany AS CustName, Total > > FROM Orders > > UNION > > SELECT CompanyName AS CustName, Total > > FROM Invoices > > GROUP BY CustName > > The GROUP BY in this statement affects only the second query (FROM Invoices), not the result of the union. > > On the contrary, the ORDER BY clause applies to the final result of the UNION operation, so ORDER BY "CustCompany" will order the entire result set by the first column of the entire resulting table. > > The reason why you can use "CustCompany" and not "CompanyName" is because the column names in the resulting table of a UNION are the columns names of the first table in the UNION. So it seems everything is working as expected. > > There is however a detail I can't answer (because I don't know the answer , that is why can't "CustName" be used instead of "CustCompany" in the ORDER BY. > > Also, if what you want is not only to sort but also to group the entire result table, then you can do that as follows: > > SELECT CustName, Total FROM( > SELECT CustCompany AS CustName, Total > FROM Orders > UNION > SELECT CompanyName AS CustName, Total > FROM Invoices > ) as TempTbl > GROUP BY CustName I don't have EDB yet, but I would try ORDER BY 1 ie BY <column number> -- |
Sat, Sep 20 2008 1:00 PM | Permanent Link |
Fernando Dias Team Elevate | Malcolm,
> I don't have EDB yet, but I would try ORDER BY 1 > ie BY <column number> That's not going to work (at least as you are expecting . In EDB, ORDER BY expressions are no longer interpreted as columns order numbers but as expressions whose value will be the value used in sorting. For example, if you have a table T, with an integer column x: T(x) ---- 1 2 3 ---- The statement SELECT x FROM T ORDER BY 10-x will sort the table according to the expression 10-x evaluated for each row, that are the values 9,8,7 so the result will be: T(x) ---- 3 2 1 -- Fernando Dias [Team Elevate] |
Sat, Sep 20 2008 5:41 PM | Permanent Link |
"Malcolm" | Fernando Dias wrote:
> That's not going to work (at least as you are expecting . Uh, oh! Then that's another reason to dread the impending upgrade. Malcolm |
Sat, Sep 20 2008 7:32 PM | Permanent Link |
Fernando Dias Team Elevate | Malcolm,
> Uh, oh! Then that's another reason to dread the impending upgrade. At the moment, I'm converting an old app from BDE (Paradox) to EDB, and after all it's not so scary as it seemed when I started. It's a lot of work, yes, because the application is big and the details vanished from my mind a long time ago, but at the end it will worth the effort, I think. -- Fernando Dias [Team Elevate] |
Mon, Sep 22 2008 1:05 AM | Permanent Link |
"David Cornelius" | > The GROUP BY in this statement affects only the second query (FROM
> Invoices), not the result of the union. > > On the contrary, the ORDER BY clause applies to the final result of > the UNION operation, so ORDER BY "CustCompany" will order the entire > result set by the first column of the entire resulting table. > > The reason why you can use "CustCompany" and not "CompanyName" is > because the column names in the resulting table of a UNION are the > columns names of the first table in the UNION. So it seems everything > is working as expected. Thanks very much--that makes a lot of sense. > There is however a detail I can't answer (because I don't know the > answer , that is why can't "CustName" be used instead of > "CustCompany" in the ORDER BY. That's one that really surprised me--seems contrary to the documentation. > Also, if what you want is not only to sort but also to group the > entire result table, then you can do that as follows: > > SELECT CustName, Total FROM( > SELECT CustCompany AS CustName, Total > FROM Orders > UNION > SELECT CompanyName AS CustName, Total > FROM Invoices > ) as TempTbl > GROUP BY CustName Neat trick! I thought about using temp tables, but thought it would be more complex than that. Thanks for providing this alternative. -- David Cornelius CorneliusConcepts.com |
Mon, Sep 22 2008 8:15 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< This, however, does NOT work. I get error 401, CustName does not exist. So I tried changing the ORDER BY clause to use the first statement's "CustName" field, which worked. >> I'll have to check into this and get back to you. There's obviously something wrong here, but I'm not sure what it is. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Sep 22 2008 12:24 PM | Permanent Link |
"David Cornelius" | > I'll have to check into this and get back to you. There's obviously
> something wrong here, but I'm not sure what it is. Thanks. -- David Cornelius CorneliusConcepts.com |
Mon, Sep 22 2008 6:06 PM | Permanent Link |
"Ole Willy Tuv" | Tim,
<< I'll have to check into this and get back to you. There's obviously something wrong here, but I'm not sure what it is. >> The ORDER BY clause should work directly on the result of processing the query expression. The "CustName" column is belonging to the cursor/result set (albeit derived from the "Orders"."CustCompany" and "Invoices"."CompanyName" source columns in the query expression), and is therefore a valid and highly logical sort specification. I also think that SQL:2003/2008 alllows source column references in the sort specification. Best regards, Ole Willy Tuv |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |