Professional Web Applications Themes

group by without aggregates - MySQL

Hi all, I have the following query: select * from teams group by teamid multiple rows may have the same teamid. The above query ensures that each row in the result set has a distinct teamid, which is the behaviur I want, but I can't figure out *why* it works. I know that the group by statement defines what you wish to aggregate over, but the above query has no aggregate functions, so I'm not quite sure why it returns distinct team ids. Does anyone know? Thanks Taras...

  1. #1

    Default group by without aggregates

    Hi all,

    I have the following query:

    select * from teams group by teamid

    multiple rows may have the same teamid. The above query ensures that
    each row in the result set has a distinct teamid, which is the
    behaviur I want, but I can't figure out *why* it works. I know that
    the group by statement defines what you wish to aggregate over, but
    the above query has no aggregate functions, so I'm not quite sure why
    it returns distinct team ids. Does anyone know?

    Thanks

    Taras

    Taras_96 Guest

  2. #2

    Default Re: group by without aggregates

    Taras_96 schrieb: 

    Of a set of rows that have the same teamid, it will simply return the
    first one that it comes across.
    This behavior does not conform to the SQL standard AFAIK.

    I use it to debug my GROUP BY clauses. I compare the results with and
    without GROUP BY and check that it's indeed doing the kind of
    aggregation I wanted it to do.

    I never use it in production code. I'm not sure that ORDER BY would help
    selecting the row that I'd actually want; I suspect that sorting happens
    after selecting the rows (conceptually) - i.e. there might be cases
    where it works and cases where it doesn't, depending on what the
    optimizer did with the query (and it may choose different strategies,
    depending on query specifity, accuracy of row statistics, mysql version,
    and phase of the moon). So I end up with behaviour that I can't fully
    test and which might change with the next version of mysql anyway.
    Let me repeat: I never use it in production code.
    (This might change once the mysql docs give a strong guarantee to nail
    down the semantics of this kind of construct. It is possible that such a
    guarantee actually exists and I overlooked it... though I'd read very
    carefully anyway: such policies are more subject to change, and my
    impression is that mysql is moving towards better SQL conformance, not
    necessarily towards closing any gaps in nonstandard semantics.)

    Regards,
    Jo
    Joachim Guest

  3. #3

    Default Re: group by without aggregates

    On May 5, 6:12 pm, Joachim Durchholz <org> wrote: 


    >
    > Of a set of rows that have the same teamid, it will simply return the
    > first one that it comes across.
    > This behavior does not conform to the SQL standard AFAIK.
    >
    > I use it to debug my GROUP BY clauses. I compare the results with and
    > without GROUP BY and check that it's indeed doing the kind of
    > aggregation I wanted it to do.
    >
    > I never use it in production code. I'm not sure that ORDER BY would help
    > selecting the row that I'd actually want; I suspect that sorting happens
    > after selecting the rows (conceptually) - i.e. there might be cases
    > where it works and cases where it doesn't, depending on what the
    > optimizer did with the query (and it may choose different strategies,
    > depending on query specifity, accuracy of row statistics, mysql version,
    > and phase of the moon). So I end up with behaviour that I can't fully
    > test and which might change with the next version of mysql anyway.
    > Let me repeat: I never use it in production code.
    > (This might change once the mysql docs give a strong guarantee to nail
    > down the semantics of this kind of construct. It is possible that such a
    > guarantee actually exists and I overlooked it... though I'd read very
    > carefully anyway: such policies are more subject to change, and my
    > impression is that mysql is moving towards better SQL conformance, not
    > necessarily towards closing any gaps in nonstandard semantics.)
    >
    > Regards,
    > Jo[/ref]


    Thanks Jo,

    I had a feeling that it wasn't standard SQL as the results aren't
    deterministic.

    Taras

    Taras_96 Guest

Similar Threads

  1. Hash aggregates ing out memory
    By Mike in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: February 26th, 03:12 PM
  2. create aggregates to concatenate
    By javier in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: February 8th, 03:30 PM
  3. Non-aggregate values attached to aggregates?
    By Benjamin Smith in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: December 17th, 03:46 AM
  4. Aggregates not allowed in WHERE clause?
    By Frans in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: December 16th, 06:22 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