ORDER BY question

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_I
D
= 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 wor
k
in Oracle and SQL Server. Thanks.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" <yodarules@.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.|||"yodarules" <yodarules@.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.
Try:
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
--|||"yodarules" <yodarules@.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 know if this will work, but you might try:
Order by 3 DESC
3 being the column's ordinal position in the SELECT list.
Rick Sawtell|||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 th
e
> 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 w
ork
> 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.
"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 don't understand the 'x' before the ORDER BY, could you explain that pleas
e.
"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" <yodarules@.discussions.microsoft.com> wrote in message
> news:D4C09490-E189-4524-95FF-744CCE7F3A7A@.microsoft.com...
>
>|||a derived table must have an alias - 'x' is as good as any. :)
yodarules wrote:
> I don't understand the 'x' before the ORDER BY, could you explain that ple
ase.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||>I don't understand the 'x' before the ORDER BY, could you explain that
>please.
>
The X is an alias for the derived table (the bracketed query after the FROM
clause).
David Portas
SQL Server MVP
--|||so don't display it :)
yodarules wrote:
> Thanks guys,
> I already tried that, but adding another columns in the select list is rul
ed
> 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 cas
e
> giving the position is only going to do it for that one column.
> "Trey Walpole" wrote:
>