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

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

>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?

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.

>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..?

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.

>thx in advance and be patient about my crap english

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 Nuyt wrote:
> On Mon, 23 Oct 2006 16:30:46 +0200, "salvatore scarantino"
> <totoscarantino@libero.it> wrote:
>
>
>>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?
>
>
> 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.
>

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.
jstucklex@attglobal.net
==================

salvatore scarantino wrote:
> 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

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.