|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ORDER BY questionI'm having some issuses with ORDER BY with my query. I'm trying to run the
same query in Oracle and SQL Server. select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA) AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP_ID = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC This work fine in SQL Server but not in Oracle, if I replace ORDER BY sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not SQL Server. Any idea how I can achive the same results but one query to work in Oracle and SQL Server. Thanks.
Show quote
"yodarules" <yodaru***@discussions.microsoft.com> wrote in message I don't know if this will work, but you might try:news:D4C09490-E189-4524-95FF-744CCE7F3A7A@microsoft.com... > I'm having some issuses with ORDER BY with my query. I'm trying to run > the > same query in Oracle and SQL Server. > > select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", > SUM(a.CA) > AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= > 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND > GROUP_ID > = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC > > This work fine in SQL Server but not in Oracle, if I replace ORDER BY > sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not > SQL Server. Any idea how I can achive the same results but one query to > work > in Oracle and SQL Server. Thanks. Order by 3 DESC 3 being the column's ordinal position in the SELECT list. Rick Sawtell
Show quote
"yodarules" <yodaru***@discussions.microsoft.com> wrote in message Try:news:D4C09490-E189-4524-95FF-744CCE7F3A7A@microsoft.com... > I'm having some issuses with ORDER BY with my query. I'm trying to run > the > same query in Oracle and SQL Server. > > select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", > SUM(a.CA) > AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= > 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND > GROUP_ID > = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC > > This work fine in SQL Server but not in Oracle, if I replace ORDER BY > sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not > SQL Server. Any idea how I can achive the same results but one query to > work > in Oracle and SQL Server. Thanks. SELECT app_id, user_id, cb, sb, ca, sa FROM (SELECT a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA) AS "CA", SUM(a.SA) AS "SA", SUM(a.CB)+SUM(a.SB) AS ord FROM APP_USER a WHERE a.START_TIME >= 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP_ID = 50 GROUP BY a.APP_ID, a.USER_ID) AS T ORDER BY ord DESC ; -- David Portas SQL Server MVP -- How about using a derived table?
Not sure if this is the exact syntax in Oracle (might want to post to an Oracle group!) but this should work in SQL Server: SELECT APP_ID, USER_ID, CB, SB, CA, SA FROM ( select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA) AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP_ID = 50 GROUP BY a.APP_ID, a.USER_ID ) x ORDER BY CB+SB DESC; Show quote "yodarules" <yodaru***@discussions.microsoft.com> wrote in message news:D4C09490-E189-4524-95FF-744CCE7F3A7A@microsoft.com... > I'm having some issuses with ORDER BY with my query. I'm trying to run > the > same query in Oracle and SQL Server. > > select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", > SUM(a.CA) > AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= > 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND > GROUP_ID > = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC > > This work fine in SQL Server but not in Oracle, if I replace ORDER BY > sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not > SQL Server. Any idea how I can achive the same results but one query to > work > in Oracle and SQL Server. Thanks. I don't understand the 'x' before the ORDER BY, could you explain that please.
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > How about using a derived table? > > Not sure if this is the exact syntax in Oracle (might want to post to an > Oracle group!) but this should work in SQL Server: > > > SELECT > APP_ID, > USER_ID, > CB, > SB, > CA, > SA > FROM > ( > select > a.APP_ID, > a.USER_ID, > SUM(a.CB) AS "CB", > SUM(a.SB) AS "SB", > SUM(a.CA) AS "CA", > SUM(a.SA) AS "SA" > FROM > APP_USER a > WHERE > a.START_TIME >= 1135044000000 > AND a.START_TIME < 1135047600000 > AND a.APP_ID = 56 > AND GROUP_ID = 50 > GROUP BY > a.APP_ID, > a.USER_ID > ) x > ORDER BY > CB+SB DESC; > > > "yodarules" <yodaru***@discussions.microsoft.com> wrote in message > news:D4C09490-E189-4524-95FF-744CCE7F3A7A@microsoft.com... > > I'm having some issuses with ORDER BY with my query. I'm trying to run > > the > > same query in Oracle and SQL Server. > > > > select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", > > SUM(a.CA) > > AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= > > 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND > > GROUP_ID > > = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC > > > > This work fine in SQL Server but not in Oracle, if I replace ORDER BY > > sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not > > SQL Server. Any idea how I can achive the same results but one query to > > work > > in Oracle and SQL Server. Thanks. > > > >I don't understand the 'x' before the ORDER BY, could you explain that The X is an alias for the derived table (the bracketed query after the FROM >please. > clause). -- David Portas SQL Server MVP -- a derived table must have an alias - 'x' is as good as any. :)
yodarules wrote: Show quote > I don't understand the 'x' before the ORDER BY, could you explain that please. > > "Aaron Bertrand [SQL Server MVP]" wrote: > > >>How about using a derived table? >> >>Not sure if this is the exact syntax in Oracle (might want to post to an >>Oracle group!) but this should work in SQL Server: >> >> >>SELECT >> APP_ID, >> USER_ID, >> CB, >> SB, >> CA, >> SA >>FROM >> ( >> select >> a.APP_ID, >> a.USER_ID, >> SUM(a.CB) AS "CB", >> SUM(a.SB) AS "SB", >> SUM(a.CA) AS "CA", >> SUM(a.SA) AS "SA" >> FROM >> APP_USER a >> WHERE >> a.START_TIME >= 1135044000000 >> AND a.START_TIME < 1135047600000 >> AND a.APP_ID = 56 >> AND GROUP_ID = 50 >> GROUP BY >> a.APP_ID, >> a.USER_ID >> ) x >>ORDER BY >> CB+SB DESC; >> >> >>"yodarules" <yodaru***@discussions.microsoft.com> wrote in message >>news:D4C09490-E189-4524-95FF-744CCE7F3A7A@microsoft.com... >> >>>I'm having some issuses with ORDER BY with my query. I'm trying to run >>>the >>>same query in Oracle and SQL Server. >>> >>>select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", >>>SUM(a.CA) >>>AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= >>>1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND >>>GROUP_ID >>>= 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC >>> >>>This work fine in SQL Server but not in Oracle, if I replace ORDER BY >>>sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not >>>SQL Server. Any idea how I can achive the same results but one query to >>>work >>>in Oracle and SQL Server. Thanks. >> >> >> how about just adding a column for
sum(a.CB+a.SB) as CBSB and then order by CBSB desc yodarules wrote: Show quote > I'm having some issuses with ORDER BY with my query. I'm trying to run the > same query in Oracle and SQL Server. > > select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA) > AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= > 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP_ID > = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC > > This work fine in SQL Server but not in Oracle, if I replace ORDER BY > sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not > SQL Server. Any idea how I can achive the same results but one query to work > in Oracle and SQL Server. Thanks. Thanks guys,
I already tried that, but adding another columns in the select list is ruled out, since we don't need the sum of these there. Its only for display purpose that we need the sum of these two columns. The reply by Rick, what I need is the sum of the two columns, in your case giving the position is only going to do it for that one column. Show quote "Trey Walpole" wrote: > how about just adding a column for > > sum(a.CB+a.SB) as CBSB > > and then > > order by CBSB desc > > > yodarules wrote: > > I'm having some issuses with ORDER BY with my query. I'm trying to run the > > same query in Oracle and SQL Server. > > > > select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA) > > AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= > > 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP_ID > > = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC > > > > This work fine in SQL Server but not in Oracle, if I replace ORDER BY > > sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not > > SQL Server. Any idea how I can achive the same results but one query to work > > in Oracle and SQL Server. Thanks. > so don't display it :)
yodarules wrote: Show quote > Thanks guys, > > I already tried that, but adding another columns in the select list is ruled > out, since we don't need the sum of these there. Its only for display > purpose that we need the sum of these two columns. > > The reply by Rick, what I need is the sum of the two columns, in your case > giving the position is only going to do it for that one column. > > "Trey Walpole" wrote: > > >>how about just adding a column for >> >>sum(a.CB+a.SB) as CBSB >> >>and then >> >>order by CBSB desc >> >> >>yodarules wrote: >> >>>I'm having some issuses with ORDER BY with my query. I'm trying to run the >>>same query in Oracle and SQL Server. >>> >>>select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA) >>>AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= >>>1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP_ID >>>= 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC >>> >>>This work fine in SQL Server but not in Oracle, if I replace ORDER BY >>>sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not >>>SQL Server. Any idea how I can achive the same results but one query to work >>>in Oracle and SQL Server. Thanks. >> Does this run in Oracle?
select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA) AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP_ID = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY SUM(a.CB)+SUM(a.SB) DESC Gert-Jan yodarules wrote: Show quote > > I'm having some issuses with ORDER BY with my query. I'm trying to run the > same query in Oracle and SQL Server. > > select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA) > AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >= > 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP_ID > = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC > > This work fine in SQL Server but not in Oracle, if I replace ORDER BY > sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not > SQL Server. Any idea how I can achive the same results but one query to work > in Oracle and SQL Server. Thanks.  |
|||||||||||||||||||||||
Other interesting topics