anssiman@[EMAIL PROTECTED]
wrote:
> is there any way to cheat group by -statement or make the query in
It isn't cheating, it's using GROUP BY as it's do***ented to work.
> other way? I mean that query:
>
> SELECT userid, name, MAX(age) FROM users GROUP BY name;
>
> produces list of the oldest users grouped by the name, but the
> userid is also available (this way it is not valid at all).
>
> Table contains rows:
> ...
> 12 | Jack | 18
> 13 | Jim | 22
> 14 | Jack | 56
> ...
>
> and the query above should return:
> 13 | Jim | 22
> 14 | Jack | 56
>
> Keen to know if there is solution!
There is.
This is a SQL question, not a Java one, in the strictest sense, but every
Java
(or C#, or VB) programmer needs to understand SQL fundamentals. I use
PostgreSQL as my standard, others use Derby ("Java Data Base") because it
comes with Java these days. Both are quite compliant.
The GROUP BY only works (correctly) when every column of the SELECT is
either
in the GROUP BY clause, or aggregated.
You did neither with 'userid'. Your particular SQL dialect accepted that,
and
created a new row header from the unGROUPed column.
Either do not show userid at all, appropriate if it is an auto-sequenced
column, or apply MAX() or MIN() to it, a dodge for when the column does
have a
business-domain meaning.
SELECT name, MAX(userid) AS userid, MAX(age)
FROM users GROUP BY name
ORDER BY name;
It's also a good idea to match GROUP BY with ORDER BY.
--
Lew
|
