I added information about this in the Order By <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy#LanguageManualSortBy-SyntaxofOrderBy> section of the wiki. Thanks, Mich and Gopal!
-- Lefty On Sun, Mar 29, 2015 at 5:24 AM, Mich Talebzadeh <m...@peridale.co.uk> wrote: > Hi Lefty, Gopal, > > > > It appears that ORDER BY 3 is not interpreted as ORDERR BY “the result set > column three” which standard SQL as evident from Oracle and Sybase does. > > > > So I made it an ORDER BY from the result set EXPLICITELY as shown below > and it worked OK > > > > SELECT > > rs.Customer_ID > > , rs.Number_of_orders > > , rs.Total_customer_amount > > , rs.Average_order > > , rs.Standard_deviation > > FROM > > ( > > SELECT cust_id AS Customer_ID, > > COUNT(amount_sold) AS Number_of_orders, > > SUM(amount_sold) AS Total_customer_amount, > > AVG(amount_sold) AS Average_order, > > STDDEV(amount_sold) AS Standard_deviation > > FROM sales > > GROUP BY cust_id > > HAVING SUM(amount_sold) > 94000 > > AND AVG(amount_sold) < STDDEV(amount_sold) > > ) rs > > ORDER BY > > *rs.Total_customer_amount* > > ; > > > > > > > +-----------------+----------------------+---------------------------+---------------------+------------------------+--+ > > | rs.customer_id | rs.number_of_orders | rs.total_customer_amount | > rs.average_order | rs.standard_deviation | > > > +-----------------+----------------------+---------------------------+---------------------+------------------------+--+ > > | 1743.0 | 238 | 94786.12999999993 | > 398.2610504201678 | 581.0439095219863 | > > | 429.0 | 231 | 94819.41000000006 | > 410.4736363636366 | 613.7057080691426 | > > | 2994.0 | 227 | 94862.61000000006 | > 417.89696035242315 | 623.1607772763742 | > > | 6395.0 | 268 | 97010.47999999998 | > 361.97940298507456 | 576.9120977984521 | > > | 12783.0 | 240 | 97573.54999999996 | > 406.5564583333332 | 590.4445500393804 | > > | 4974.0 | 235 | 98006.16000000002 | > 417.0474893617022 | 624.337482834059 | > > | 42167.0 | 266 | 98585.96000000002 | > 370.6239097744362 | 590.965120684093 | > > | 10747.0 | 256 | 99578.08999999997 | > 388.9769140624999 | 600.7615005975689 | > > | 11407.0 | 248 | 103412.65999999995 | > 416.9865322580643 | 622.221465710723 | > > > +-----------------+----------------------+---------------------------+---------------------+------------------------+--+ > > 9 rows selected (209.699 seconds) > > > > > > Regards, > > > > Mich Talebzadeh > > > > http://talebzadehmich.wordpress.com > > > > *Publications due shortly:* > > *Creating in-memory Data Grid for Trading Systems with Oracle TimesTen and > Coherence Cache* > > > > NOTE: The information in this email is proprietary and confidential. This > message is for the designated recipient only, if you are not the intended > recipient, you should destroy it immediately. Any information in this > message shall not be understood as given or endorsed by Peridale Ltd, its > subsidiaries or their employees, unless expressly so stated. It is the > responsibility of the recipient to ensure that this email is virus free, > therefore neither Peridale Ltd, its subsidiaries nor their employees accept > any responsibility. > > > > *From:* Mich Talebzadeh [mailto:m...@peridale.co.uk] > *Sent:* 29 March 2015 00:11 > *To:* user@hive.apache.org > *Subject:* ORDER BY clause in Hive > > > > Hi, > > > > Can someone point me to doc or otherwise to see if ORDER BY clause in Hive > is working OK > > > > I have a simple aggregate query as follows: > > > > SELECT cust_id AS Customer_ID, > > COUNT(amount_sold) AS Number_of_orders, > > SUM(amount_sold) AS Total_customer_amount, > > AVG(amount_sold) AS Average_order, > > STDDEV(amount_sold) AS Standard_deviation > > FROM sales > > GROUP BY cust_id > > HAVING SUM(amount_sold) > 94000 > > AND AVG(amount_sold) < STDDEV(amount_sold) > > ORDER BY 3 ; > > > > The original table and data are from Oracle sh.sales table > > > > Oracle comes back for this query with > > > > CUSTOMER_ID NUMBER_OF_ORDERS TOTAL_CUSTOMER_AMOUNT AVERAGE_ORDER > STANDARD_DEVIATION > > ----------- ---------------- --------------------- ------------- > ------------------ > > 1743 238 94786.13 398.26105 > 582.26845 > > 429 231 94819.41 410.473636 > 615.038404 > > 2994 227 94862.61 417.89696 > 624.53793 > > 6395 268 97010.48 361.979403 > 577.991448 > > 12783 240 97573.55 406.556458 > 591.6785 > > 4974 235 98006.16 417.047489 > 625.670115 > > 42167 266 98585.96 370.62391 > 592.079099 > > 10747 256 99578.09 388.976914 > 601.938312 > > 11407 248 103412.66 416.986532 > 623.479751 > > > > 9 rows selected. > > > > Ordered by TOTAL_CUSTOMER_AMOUNT > > > > And hive returns for the same query > > > > > +--------------+-------------------+------------------------+---------------------+---------------------+--+ > > | customer_id | number_of_orders | total_customer_amount | > average_order | standard_deviation | > > > +--------------+-------------------+------------------------+---------------------+---------------------+--+ > > | 42167.0 | 266 | 98585.96000000002 | > 370.6239097744362 | 590.965120684093 | > > | 12783.0 | 240 | 97573.54999999996 | > 406.5564583333332 | 590.4445500393804 | > > | 11407.0 | 248 | 103412.65999999995 | > 416.9865322580643 | 622.221465710723 | > > | 10747.0 | 256 | 99578.08999999997 | > 388.9769140624999 | 600.7615005975689 | > > | 6395.0 | 268 | 97010.47999999998 | > 361.97940298507456 | 576.9120977984521 | > > | 4974.0 | 235 | 98006.16000000002 | > 417.0474893617022 | 624.337482834059 | > > | 2994.0 | 227 | 94862.61000000006 | > 417.89696035242315 | 623.1607772763742 | > > | 1743.0 | 238 | 94786.12999999993 | > 398.2610504201678 | 581.0439095219863 | > > | 429.0 | 231 | 94819.41000000006 | > 410.4736363636366 | 613.7057080691426 | > > > +--------------+-------------------+------------------------+---------------------+---------------------+--+ > > 9 rows selected (215.774 seconds) > > > > But ordering in Hive does not seem to be correct! Please note ordering is > on column three, *total_customer_amount* > > > > I also tried this in Sybase and got the same as Oracle. > > > > Adaptive Server cpu time: 100 ms. > > Customer_ID Number_of_orders > Total_customer_amount > Average_order Standard_deviation > > -------------------- ---------------- > ----------------------------------------- > ---------------------------------------------------- > --------------------------- > > 1743 238 > 94786.13 > 398.2610504201680 582.268450 > > 429 231 > 94819.41 > 410.4736363636363 615.038404 > > 2994 227 > 94862.61 > 417.8969603524229 624.537930 > > 6395 268 > 97010.48 > 361.9794029850746 577.991448 > > 12783 240 > 97573.55 > 406.5564583333333 591.678500 > > 4974 235 > 98006.16 > 417.0474893617021 625.670115 > > 42167 266 > 98585.96 > 370.6239097744360 592.079099 > > 10747 256 > 99578.09 388.9769140625000 > 601.938312 > > 11407 248 > 103412.66 > 416.9865322580645 623.479751 > > > > I tried Google search and seems to be different suggestions. May be I have > to rewrite the code? > > > > Thanks > > > > Mich Talebzadeh > > > > http://talebzadehmich.wordpress.com > > > > *Publications due shortly:* > > *Creating in-memory Data Grid for Trading Systems with Oracle TimesTen and > Coherence Cache* > > > > NOTE: The information in this email is proprietary and confidential. This > message is for the designated recipient only, if you are not the intended > recipient, you should destroy it immediately. Any information in this > message shall not be understood as given or endorsed by Peridale Ltd, its > subsidiaries or their employees, unless expressly so stated. It is the > responsibility of the recipient to ensure that this email is virus free, > therefore neither Peridale Ltd, its subsidiaries nor their employees accept > any responsibility. > > >
