# Math in ORDER BY? - Microsoft SQL / MS SQL Server

Is it possible to use math in the ORDER BY clause? I need the percent of views by divide the number of bannerviews to the sum of all bannerviews and sort by the less viewed banner SELECT * FROM banner WHERE (startdate <= '2003-07-14' AND stopdate >= '2003-07-14') AND ad_group = '2' ORDER BY views / " & itmp & " DESC,ad_size Best Regards Johan Ryberg...

1. ## Math in ORDER BY?

Is it possible to use math in the ORDER BY clause?

I need the percent of views by divide the number of bannerviews to the sum
of all bannerviews and sort by the less viewed banner

SELECT * FROM banner WHERE (startdate <= '2003-07-14' AND stopdate >=
'2003-07-14') AND ad_group = '2' ORDER BY views / " & itmp & " DESC,ad_size

Best Regards Johan Ryberg

Johan Guest

2. ## Re: Math in ORDER BY?

you can use mathematics in the order by clause .
See the following hypothetical example on the northwind database.
Ex:
declare x int
select x=count(*) from orders
select employeeid * x,employeeid from orders order by employeeid * x

--
-Vishal
"Johan Ryberg" <com> wrote in message
news:#bK\$phx.gbl...

Vishal Guest

3. ## Re: Math in ORDER BY?

You certainly can put calculations after ORDER BY. But if I've understood
you correctly you don't need to.

You want:

SELECT *
FROM banner
WHERE (startdate <= '2003-07-14' AND stopdate >='2003-07-14')
ORDER BY [views] / (SELECT SUM([views])
FROM banner)

Which will give you the same order as:

SELECT *
FROM banner
WHERE (startdate <= '2003-07-14' AND stopdate >='2003-07-14')

without any calculation.

--
David Portas
------------
--

David Guest

4. ## Re: Math in ORDER BY?

aah,

no, the "(SELECT SUM([views])" that you added is a global variabel
independent of this database.

SELECT *
FROM banner
WHERE (startdate <= '2003-07-14' AND stopdate >='2003-07-14')
ORDER BY [views] / 545454 <---- "GLOBAL COUNTER OF VIEWED PAGES"

BUT!

If I do this it seems like the MS-SQL is NOT doing any calculation. The
result is unsorted

Best Regards Johan Ryberg

"David Portas" <org> skrev i meddelandet
news:phx.gbl...

Johan Guest

5. ## Re: Math in ORDER BY?

Hi,

I got 0 as all results.

I know that the result will be decimal, like 0.001 up to 1.0 as maxumum.
Must all result be integers?

Best Regards Johan Ryberg

"Vishal Parkar" <com> skrev i meddelandet
news:%phx.gbl...
give
> meddelandet [/ref]
> understood
> >
> >[/ref]
>
>[/ref]

Johan Guest

6. ## Re: Math in ORDER BY?

Use datatype DECIMAL for proper precision, or do explicit conversion using
CONVERT function.
See following example.

declare x int
select x = 5
select convert(decimal (10,5),7)/x, 7/x

--
-Vishal

"Johan Ryberg" <com> wrote in message
news:#phx.gbl...
> give [/ref][/ref]
The
> > meddelandet
> > understood
> >
> >[/ref]
>
>[/ref]

Vishal Guest

7. ## Re: Math in ORDER BY?

It's no constant, I just used that as an example because the 545454 is from
a global variable that is not in any database do instread of make you
thought I got the value from a table i just wrote a number.

Best Regards Johan Ryberg

"David Portas" <org> skrev i meddelandet
news:phx.gbl...
>
>
> There's no point in dividing an ORDER BY column by a constant. The[/ref]
ordering [/ref][/ref]
will [/ref]
> The
> >
> >[/ref]
>
>[/ref]

Johan Guest

8. ## Re: Math in ORDER BY?

I had to make the tabel DECIMAL even if the values are INTEGERS to make the
result give the convertstring any decimals to work with but since I did
change the table I could remove the convert function :-S

Is the result in the MS-SQL Query yser not returning decimals if the
tabel does not support decimals?!

Best Regards Johan Ryberg

"Johan Ryberg" <com> skrev i meddelandet
news:phx.gbl...
from
meddelandet
> >
> >
> > There's no point in dividing an ORDER BY column by a constant. The[/ref]
> ordering [/ref][/ref]
maxumum. [/ref]
> will [/ref][/ref]
calculation.
> >
> >[/ref]
>
>[/ref]

Johan Guest

9. ## Re: Math in ORDER BY?

A global or local variable is still constant in a SELECT statement. :-)

--
David Portas
------------
--

"Johan Ryberg" <com> wrote in message
news:phx.gbl...
from
meddelandet
> >
> >
> > There's no point in dividing an ORDER BY column by a constant. The[/ref]
> ordering [/ref][/ref]
maxumum. [/ref]
> will [/ref][/ref]
calculation.
> >
> >[/ref]
>
>[/ref]

David Guest

10. ## Re: Math in ORDER BY?

DECLARE cnt INTEGER

SET cnt = 4

CREATE TABLE banner ([views] INTEGER PRIMARY KEY)

INSERT INTO banner VALUES (1)
INSERT INTO banner VALUES (2)
INSERT INTO banner VALUES (3)

SELECT [views]/CAST(cnt AS REAL)
FROM banner

--
David Portas
------------
--

"Johan Ryberg" <com> wrote in message
news:#phx.gbl...
the
> from
> meddelandet
> > ordering [/ref][/ref]
and [/ref]
> maxumum.
> > will [/ref][/ref]
variabel [/ref][/ref]
PAGES" [/ref]
> calculation.
> >
> >[/ref]
>
>[/ref]

David Guest

11. ## Re: Math in ORDER BY?

Johan,

You can cast the value in the select statement, for example
SELECT ...
ORDER BY [views] / CAST(545454 AS decimal(19,4))

But David Portas gave some good advice. If the value will not change
during the execution of the statement (which is the case with global
variables), then using it in the ORDER BY clause is not useful.

In fact, it can hurt the result, as the following examples shows.

create table #t(i int)
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)

-- Query 1
select *,i as sortorder from #t order by i
-- Query 2
select *,i / 50 as sortorder from #t order by i / 50, i
-- Query 3
select *,i / 50 as sortorder from #t order by i / 50, i desc
-- Query 4
select *,i / .5 as sortorder from #t order by i / .5, i
-- Query 5
select *,i / -.5 as sortorder from #t order by i / -.5, i

drop table #t

Queries 2 and 3 shows that you lose precision when dividing by a large
integer number. This is why Query 3 returns in a different order as
query 2.

Note that the order does not change with the magnitude of the constant,
but only by its sign (positive or negative). So if you know that the
global variable will always be > 0, then you can just omit it.

Hope this helps,
Gert-Jan

Johan Ryberg wrote:
> from
> meddelandet
> > ordering [/ref][/ref]
<snip>
Gert-Jan Guest

12. ## Problem SOLVED! 1000 thanx!!!

Thank you all for helping me, I did manage to solve the problem with all of

THANKS DUDES!!!

Best Regards Johan Ryberg

"Johan Ryberg" <com> skrev i meddelandet
news:%phx.gbl...
the
> from
> meddelandet
> > ordering [/ref][/ref]
and [/ref]
> maxumum.
> > will [/ref][/ref]
variabel [/ref][/ref]
PAGES" [/ref]
> calculation.
> >
> >[/ref]
>
>[/ref]

Johan Guest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•