Professional Web Applications Themes

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. #1

    Default 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. #2

    Default 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... 
    DESC,ad_size 


    Vishal Guest

  3. #3

    Default 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')
    AND ad_group = '2'
    ORDER BY [views] / (SELECT SUM([views])
    FROM banner)
    , ad_size

    Which will give you the same order as:

    SELECT *
    FROM banner
    WHERE (startdate <= '2003-07-14' AND stopdate >='2003-07-14')
    AND ad_group = '2'
    ORDER BY [views], ad_size

    without any calculation.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default 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')
    AND ad_group = '2'
    ORDER BY [views] / 545454 <---- "GLOBAL COUNTER OF VIEWED PAGES"
    ,ad_size


    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. #5

    Default 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. #6

    Default 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. #7

    Default 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. #8

    Default 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. #9

    Default Re: Math in ORDER BY?

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

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "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. #10

    Default Re: Math in ORDER BY?

    Just CAST your variable rather than change your table.

    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
    ------------
    Please reply only to the newsgroup
    --

    "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. #11

    Default 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. #12

    Default Problem SOLVED! 1000 thanx!!!

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

    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

Similar Threads

  1. math::trulyrandom
    By Daniel Miller in forum PERL Modules
    Replies: 10
    Last Post: May 25th, 06:44 PM
  2. math problem will Math.floor help
    By javman webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 5
    Last Post: February 4th, 01:28 AM
  3. Replies: 4
    Last Post: December 19th, 07:09 AM
  4. Math::RungeKutta ?
    By Peter Billam in forum PERL Modules
    Replies: 4
    Last Post: December 1st, 05:46 AM
  5. OT: Math help
    By Joseph D. DeJohn in forum ASP.NET General
    Replies: 2
    Last Post: August 7th, 02:52 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139