--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@a...> wrote:

>
> This works:
>
> SELECT
> t.COL_1/100, MIN( t.COL_2 )
> FROM
> TABLE_1 t
> GROUP BY
> 1
>
> This doesn't:
>
> SELECT
> t.COL_1/100, MIN( t.COL_2 )
> FROM
> TABLE_1 t
> GROUP BY
> t.COL_1/100
>

Yeah, "t.Col_1/100" is not a field but an expression. The only way to
group by an expression is to use the relative field number.

> Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 171
> (
>
> I can't use the first way, as I need to determine what value of t.COL_1
> is associated with the minimum value, and the WHERE clause would contain

Maybe I am not understanding this, or maybe it is just too simplified,
but the corresponding t.col_1 is the first expression multiplied by 100.

Of course if you could not even bother using the divide by 100 at the
server and your client can do the calculation when it gets the results
back, or you could change your query to.

SELECT
t.Col_1, t.COL_1/100, MIN( t.COL_2 )
FROM
TABLE_1 t
GROUP BY
t.COL_1, 2

And make the server do both. Of course, to me this seems silly. It
makes the database server do mathematical operations and increases the
fetch time and network traffic for no real benefit.

>
> t.COL_2 = (SELECT MIN( t.COL_2 ) FROM TABLE_1 t GROUP BY t.COL_1/100)

This does not make sense to me. Why are you using a group by in your
subselect here? Also, do not use the same table alias for the select
and the subselect.

t.COL_2 = (SELECT MIN( t2.COL_2 ) FROM TABLE_1 t2 where t.COL_1 =
t2.COL_1)

>
> among other conditionals.
>
>
> So my question is, why doesn't the second query work, and is there
> anything that would solve my requirement of finding out what row had the
> minimum value?

Perhaps some simplified example may help. If I understand what your
are actually trying to do, you have a table

table1
id c1 c2
1 1 1
2 1 2
3 2 1
4 2 1
5 1 6


etc

And you want to find the ID where c1 = 1 and c2 is the smallest value?

if so, a join could be used. I forget the syntax for all, but it is
something like this:

select t1.id
from table1 t1
join table1 t2 on (t1.c1 = t2.c1 and t1.c2 <= all(t2.c2))

If that fails, you could use a subselect to find the minimum c1 value
where t1.c1 = t2.c1

Adam