Professional Web Applications Themes

order the GROUP BY visual - MySQL

hi there, got a problem, im using "group by" to dont show the row duplicates, how does group by show the row? i mean does it show the 1st record that fit in the group by rule or the last one, and how i can change it? ie: got 2 rows like this: id|date| so i ue GROUP BY id, wich row will be showed? that one with the lower id? or that one with the higher, and how i can change the order on wich they are show..? thx in advance and be patient about my crap english...

  1. #1

    Default order the GROUP BY visual

    hi there, got a problem, im using "group by" to dont show the row
    duplicates, how does group by show the row? i mean does it show the 1st
    record that fit in the group by rule or the last one, and how i can change
    it?

    ie:
    got 2 rows like this: id|date|
    so i ue GROUP BY id, wich row will be showed? that one with the lower id? or
    that one with the higher, and how i can change the order on wich they are
    show..?


    thx in advance and be patient about my crap english


    salvatore Guest

  2. #2

    Default Re: order the GROUP BY visual

    On Mon, 23 Oct 2006 16:30:46 +0200, "salvatore scarantino"
    <it> wrote:
     

    If these rows are really duplicates, it wouldn't matter which
    one was shown, wouldn't it? Apparently, they are not the same,
    so they aren't duplicate at all (*).

    There is no strict rule which values will be shown for columns
    not mentioned in the GROUP BY clause. GROUP BY is meant to be
    used together with aggregate functions on every one of those
    columns, like min(), max(), floor(), ceiling(), avg(), sum()
    etcetera..
    There probably won't be something like first or last, as the
    order of rows in any result set is undefined, unless forced by a
    ORDER BY clause, but I may be wrong at that.
    Some SQL dialects even reject a query containing columns without
    aggregate funtions.
     

    There is no order, you get only one result row for every ID.
    As said, any column not mentioned in the GROUP BY clause will
    have an unpredictable value. Make it predictable with for
    example MAX() or MIN().

    You can order the resulting rows as usual with ORDER BY.

    (*) By the way, I think you are struggling with a less than
    optimal schema. Your values aren't uniquely identified by a key.
    That usually leads to this kind of questions. Feel free to ask
    help, or, even better, Google for "database design normalisation
    rdbms" (without the quotes). There is some excellent material
    out there.
     

    You're welcome, hope this helps.
    --
    ( Kees
    )
    c[_] When you say 'I wrote a program that crashed Windows' people just stare at you
    blankly and say 'Hey, I got those with the system for free' (Linus Torvalds) (#29)
    Kees Guest

  3. #3

    Default Re: order the GROUP BY visual

    Kees Nuyt wrote: 
    >
    >
    > If these rows are really duplicates, it wouldn't matter which
    > one was shown, wouldn't it? Apparently, they are not the same,
    > so they aren't duplicate at all (*).
    >
    > There is no strict rule which values will be shown for columns
    > not mentioned in the GROUP BY clause. GROUP BY is meant to be
    > used together with aggregate functions on every one of those
    > columns, like min(), max(), floor(), ceiling(), avg(), sum()
    > etcetera..
    > There probably won't be something like first or last, as the
    > order of rows in any result set is undefined, unless forced by a
    > ORDER BY clause, but I may be wrong at that.
    > Some SQL dialects even reject a query containing columns without
    > aggregate funtions.
    >[/ref]

    And, in fact, this is a MySQL extension of the SQL standard. The SQL
    standard states that all non-aggregate columns must be specified in the
    GROUP BY clause.

    So,

    SELECT id, `date` FROM mytable
    GROUP BY id

    would be invalid because it doesn't include the `date` column in the
    GROUP BY clause.

    Something like:

    SELECT id, sum(amount)
    GROUP BY id

    would be valid because sum(amount) is an aggregate function (add up all
    `amount` values for id).



    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: order the GROUP BY visual


    salvatore scarantino wrote: 

    I was under the impression that the value of the last processed field
    (closest to the bottom), would be the one that showed in the column.

    This is just a stab in the dark here, but as you clearly don't wish to
    use aggregate functions, I am guessing that the use of LEFT JOIN or
    RIGHT JOIN might be more suited to your needs?

    All the best.

    Daz.

    Daz Guest

Similar Threads

  1. group by and order
    By j0sh in forum MySQL
    Replies: 0
    Last Post: September 13th, 11:01 AM
  2. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  3. Order by results of count using 'group by'
    By salvador in forum MySQL
    Replies: 3
    Last Post: August 2nd, 06:49 PM
  4. group by and order by
    By steeban in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: May 25th, 06:56 AM
  5. MYSQL query using GROUP BY and ORDER BY?
    By Bonge Boo! in forum PHP Development
    Replies: 3
    Last Post: August 15th, 06:12 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