Professional Web Applications Themes

Q: Sorting by the sum of columns? - MySQL

Hi Folks! I'm a newbie MySQL programmer. While doing my project I have faced following problem: I have five columns in my table (indexed 'id' and four with numerical values). I would like to sum the numerical values into a kind of virtual column and sort the table accordingly to the decreasing values of the sum (of the virual column). I have searched in numerous tutorials and FAQs but have not found description of even similar situation. Please help Best regards, Genz...

  1. #1

    Default Q: Sorting by the sum of columns?

    Hi Folks!

    I'm a newbie MySQL programmer. While doing my project I have faced
    following problem:

    I have five columns in my table (indexed 'id' and four with numerical
    values). I would like to sum the numerical values into a kind of
    virtual column and sort the table accordingly to the decreasing values
    of the sum (of the virual column).

    I have searched in numerous tutorials and FAQs but have not found
    description of even similar situation.

    Please help

    Best regards,

    Genz

    genzeryk Guest

  2. #2

    Default Re: Q: Sorting by the sum of columns?

    On May 12, 3:44 pm, genzeryk <com> wrote: 

    select id, (a + b + c + d) theSum
    from someTable
    order by (a + b + c + d) desc

    ZeldorBlat Guest

  3. #3

    Default Re: Q: Sorting by the sum of columns?

    ZeldorBlat wrote: 
    >
    > select id, (a + b + c + d) theSum
    > from someTable
    > order by (a + b + c + d) desc[/ref]

    That seems needlessly wasted on resources, by summing the columns twice.
    Surely either of these 2 would be better:

    SELECT
    `id`,
    (`a` + `b` + `c` + `d`) `theSum`
    FROM `someTable`
    ORDER BY `theSum`

    or


    SELECT
    `id`,
    (`a` + `b` + `c` + `d`) `theSum`
    FROM `someTable`
    ORDER BY 2


    Paul Guest

  4. #4

    Default Re: Q: Sorting by the sum of columns?

    On May 12, 3:53 pm, "Paul Lautman" <com>
    wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > That seems needlessly wasted on resources, by summing the columns twice.
    > Surely either of these 2 would be better:
    >
    > SELECT
    > `id`,
    > (`a` + `b` + `c` + `d`) `theSum`
    > FROM `someTable`
    > ORDER BY `theSum`
    >
    > or
    >
    > SELECT
    > `id`,
    > (`a` + `b` + `c` + `d`) `theSum`
    > FROM `someTable`
    > ORDER BY 2[/ref]

    Do you know for a fact that MySQL will internally calculate the sums
    twice? If the database engine is properly optimized I would guess
    that it won't.

    ZeldorBlat Guest

  5. #5

    Default Re: Q: Sorting by the sum of columns?

    ZeldorBlat wrote: 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >>
    >> That seems needlessly wasted on resources, by summing the columns
    >> twice. Surely either of these 2 would be better:
    >>
    >> SELECT
    >> `id`,
    >> (`a` + `b` + `c` + `d`) `theSum`
    >> FROM `someTable`
    >> ORDER BY `theSum`
    >>
    >> or
    >>
    >> SELECT
    >> `id`,
    >> (`a` + `b` + `c` + `d`) `theSum`
    >> FROM `someTable`
    >> ORDER BY 2[/ref]
    >
    > Do you know for a fact that MySQL will internally calculate the sums
    > twice? If the database engine is properly optimized I would guess
    > that it won't.[/ref]

    Yes I do know that for a fact. No matter how well the database engine is
    likely to be optimised, it is not going to bother tryjng to recognise that
    two distinct pieces of calculation happen to have the same fields. That is
    why you are able to refer to previously calculated fields. Optimisers assume
    some level of intelligence from the programmer.


    Paul Guest

  6. #6

    Default Re: Q: Sorting by the sum of columns?

    On May 13, 9:42 am, "Paul Lautman" <com>
    wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Yes I do know that for a fact. No matter how well the database engine is
    > likely to be optimised, it is not going to bother tryjng to recognise that
    > two distinct pieces of calculation happen to have the same fields. That is
    > why you are able to refer to previously calculated fields. Optimisers assume
    > some level of intelligence from the programmer.[/ref]

    I was unable to find that doented anywhere. Do you have a source?
    I'm curious now...

    ZeldorBlat Guest

  7. #7

    Default Re: Q: Sorting by the sum of columns?

    On 13 May, 22:41, ZeldorBlat <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > I was unable to find that doented anywhere. Do you have a source?
    > I'm curious now...- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Sorry for the delay in answering this one. My source is the manual.
    On the page http://dev.mysql.com/doc/refman/5.0/en/select.html
    Where it says:

    "Columns selected for output can be referred to in ORDER BY and GROUP
    BY clauses using column names, column aliases, or column positions."

    And of course it defines an Order By statement as:

    ORDER BY {col_name | expr | position}

    It does not say that columns selected for output can be referred to by
    using an expression that happens to match the expression that created
    the data for that column. Thus, unless you use one of the methods to
    refer to the output column, you are left with an expression that will
    have to be evaluated.


    Captain Guest

  8. #8

    Default Re: Q: Sorting by the sum of columns?

    On May 15, 6:57 am, Captain Paralytic <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > Sorry for the delay in answering this one. My source is the manual.
    > On the pagehttp://dev.mysql.com/doc/refman/5.0/en/select.html
    > Where it says:
    >
    > "Columns selected for output can be referred to in ORDER BY and GROUP
    > BY clauses using column names, column aliases, or column positions."
    >
    > And of course it defines an Order By statement as:
    >
    > ORDER BY {col_name | expr | position}
    >
    > It does not say that columns selected for output can be referred to by
    > using an expression that happens to match the expression that created
    > the data for that column. Thus, unless you use one of the methods to
    > refer to the output column, you are left with an expression that will
    > have to be evaluated.[/ref]

    Ok, but nowhere does that say that the expression will or will not be
    internally evaluated a second time. If the database is smart enough
    to recognize when you have an expression in the group by clause that
    is not in the select list (in which case it complains), I would expect
    that it is smart enough to match an expression in the select list to
    an expression in the order by clause.

    ZeldorBlat Guest

  9. #9

    Default Re: Q: Sorting by the sum of columns?

    ZeldorBlat wrote: 
    >
    > Ok, but nowhere does that say that the expression will or will not be
    > internally evaluated a second time. If the database is smart enough
    > to recognize when you have an expression in the group by clause that
    > is not in the select list (in which case it complains), I would expect
    > that it is smart enough to match an expression in the select list to
    > an expression in the order by clause.
    >
    >[/ref]
    It's barely worth discussing. As focused as database companies are on
    performance I doubt any would re-evaluate any expression that lexically
    matches another either before or after compilation.

    Referring to column aliases is a syntactic feature, not a performance
    feature. It's a good thing that MySQL has column aliases. I remember
    that being one of my favorite features of Teradata's SQL.

    Regardless, the answer to the question is unlikely to be found in the
    manuals. To know for sure the answer would either have to come from a
    MySQL engineer or someone else familiar with MySQL's pr, compiler,
    evaluator, or looking through the code.

    However, as confident as Mr. Lautman is of the "facts," there are none
    supporting the /assumption/ the ORDER BY clause' re-evaluates duplicate
    expressions. Though I have no "facts" supporting my assumptions, I'm
    equally confident that considering SQL databases maturity, the amount of
    engineering given them, the papers published on optimization strategies,
    the competition for paying licensees, and the constant pressure to
    improve performance, that such an issue as duplicate expression
    evaluation is unlikely to have gone unnoticed for 30+ years.

    --
    Visit <http://blogs.instreamfinancial.com/anything.php>
    to read my rants on technology and the finance industry.
    Thomas Guest

  10. #10

    Default Re: Q: Sorting by the sum of columns?

    Thomas Gagne wrote: 
    >>
    >> Ok, but nowhere does that say that the expression will or will not be
    >> internally evaluated a second time. If the database is smart enough
    >> to recognize when you have an expression in the group by clause that
    >> is not in the select list (in which case it complains), I would
    >> expect that it is smart enough to match an expression in the select
    >> list to an expression in the order by clause.
    >>
    >>[/ref]
    > It's barely worth discussing. As focused as database companies are on
    > performance I doubt any would re-evaluate any expression that
    > lexically matches another either before or after compilation.
    >
    > Referring to column aliases is a syntactic feature, not a performance
    > feature. It's a good thing that MySQL has column aliases. I remember
    > that being one of my favorite features of Teradata's SQL.
    >
    > Regardless, the answer to the question is unlikely to be found in the
    > manuals. To know for sure the answer would either have to come from a
    > MySQL engineer or someone else familiar with MySQL's pr, compiler,
    > evaluator, or looking through the code.
    >
    > However, as confident as Mr. Lautman is of the "facts," there are none
    > supporting the /assumption/ the ORDER BY clause' re-evaluates
    > duplicate expressions. Though I have no "facts" supporting my
    > assumptions, I'm equally confident that considering SQL databases
    > maturity, the amount of engineering given them, the papers published
    > on optimization strategies, the competition for paying licensees, and
    > the constant pressure to improve performance, that such an issue as
    > duplicate expression evaluation is unlikely to have gone unnoticed
    > for 30+ years.[/ref]

    The manual tells you when an output column will be referenced. If it would
    be referenced in all cases then the writers would not have bothered to list
    the only 2 that applied. The manual doesn't tell you everything that does
    not apply. For instance it tells you that the SELECT syntax starts with
    SELECT. It doesn't tell you that you cannot spell SELECT as SELETC or any
    other way.

    ZeldorBlat's mention of the GROUP BY clause is totally unrelated and in fact
    wrong. It is indeed perfectly possible to have an expression in the GROUP BY
    clause that is not in the SELECT list. And it does not "complain" as he
    states. Instead it works! I just tried it.

    Regardless of what you say about optimisation strategies, the developers of
    databases do, as I have said before, assume a bit of intelligence in the
    people who use them. They have stated quite clearly in the manual when data
    in a column can be referenced. The would not bother, no matter how many
    papers had been written to try to optimise a query for something like this
    when they have already given and doented the 2 correct ways to do it.


    Paul Guest

  11. #11

    Default Re: Q: Sorting by the sum of columns?

    Paul Lautman wrote: 
    Have you used other SQL products, or is MySQL the first? How did
    grouping by expressions not in the select list work then? 
    You might assume so, but for many SQL servers optimization strategies
    routinely dispense with table and join order, and instead rely on
    indexes and statistics to decide the best query plan. I'm not a MySQL
    expert, but perhaps you've tried using (or have read about) "forceplan,"
    which for SQL Server and Sybase basically neuters the optimizer, tells
    it to stay home, and says, "I'm the programmer, do it the way I've
    written it."

    In many instances, the optimizer can be smarter than the programmer.
    And based on what I've seen from many programmers, that's not that hard
    to do. In my early days I'm pretty sure it was smarter than me. Maybe
    it still is... 
    The two correct ways to express syntax. Not ways to force or avoid
    redundant expression evaluation.

    Perhaps a SHOWPLAN might help resolve this?


    --
    Visit <http://blogs.instreamfinancial.com/anything.php>
    to read my rants on technology and the finance industry.
    Thomas Guest

  12. #12

    Default Re: Q: Sorting by the sum of columns?

    On 16 May, 04:47, Thomas Gagne <com> wrote: 


    >[/ref]
    | Have you used other SQL products, or is MySQL the first? How did
    | grouping by expressions not in the select list work then?

    I have used others, in particular DB/2. However, this discussion is
    about MySQL (this is comp.databases.mysql).
    The manual that I was refering to was the MySQL manual. I would not
    make statements about another database system without having checked
    the manuals for them. The extensions to SQL standard regarding "GROUP
    BY" that have been mentioned above may not be valid for other systems.
     
    >
    > You might assume so, but for many SQL servers optimization strategies
    > routinely dispense with table and join order, and instead rely on
    > indexes and statistics to decide the best query plan. I'm not a MySQL
    > expert, but perhaps you've tried using (or have read about) "forceplan,"
    > which for SQL Server and Sybase basically neuters the optimizer, tells
    > it to stay home, and says, "I'm the programmer, do it the way I've
    > written it."[/ref]
    But this discussion is about MySQL. It is the MySQL Manual that I was
    looking at.
     
    Maybe some of them are. But the MySQL manual is quite clear on when
    column data is referred to and it is MySQL we are discussing here.
     
    >
    > The two correct ways to express syntax. Not ways to force or avoid
    > redundant expression evaluation.
    >
    > Perhaps a SHOWPLAN might help resolve this?
    >
    > --
    > Visit <http://blogs.instreamfinancial.com/anything.php>
    > to read my rants on technology and the finance industry.[/ref]


    Captain Guest

Similar Threads

  1. Datagrid sorting on multiple columns
    By guygousse in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: November 19th, 04:17 AM
  2. cfquery sorting columns
    By savage10 in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 3rd, 06:19 PM
  3. Sorting a datagrid with autogenerated columns
    By MikeG in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: June 23rd, 04:46 PM
  4. Sorting using Dynamic Template Columns
    By Susil Patro in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: June 1st, 12:13 PM
  5. Sorting and Template Columns
    By Dave E in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: October 26th, 02:40 PM

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