This does what I need, but there is the problem where the grouping (item
ID divided by 100) will return multiple rows if the price-per-unit
resolves to the same number (i.e. a few items all resolve to 32 cents
per milliliter).

select
v1.ITEMID/100, v1.CLASSID,
v1.ITEMID,
(v1.PRICE / v1.TOT_QTY)
from
V_ITEM_PRICE v1
where
v1.ITEMID is not null and
(v1.PRICE / v1.TOT_QTY) <= ALL(
select min( v2.PRICE / v2.TOT_QTY )
from V_ITEM_PRICE v2
where v2.ITEMID/100 = v1.ITEMID/100 and v2.CLASSID = v1.CLASSID
)

The obvious fix was to group it this way:

select
v1.ITEMID/100, v1.CLASSID,
min(v1.ITEMID),
min(v1.PRICE / v1.TOT_QTY)
from
V_ITEM_PRICE v1
where
v1.ITEMID is not null and
(v1.PRICE / v1.TOT_QTY) <= ALL(
select min( v2.PRICE / v2.TOT_QTY )
from V_ITEM_PRICE v2
where v2.ITEMID/100 = v1.ITEMID/100 and v2.CLASSID = v1.CLASSID
)
group by
1,2

Thanks Adam, for reminding me of the ALL predicate.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Monday, January 23, 2006 12:05 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: Grouping on a calculation

I have a table listing items and their prices. The last two digits of
the industry defined ID relate to packaging, so grouping and dividing by
100 allows me to find the minimum price (or price-per-unit in the actual
problem) for the item, ignoring packaging differences:

SELECT
t.PRODUCTID / 100, MIN( t.PRICE )
FROM
TABLE_1 t
GROUP BY
1

Knowing the minimum price is not enough, I need to know the item that
has the minimum price. Given this table, I need to produce these
results:

Table
PRODUCTID PRICE
12300 10
12301 11
12302 12
45699 100
45655 110
45622 120

Results
PRODUCTID PRICE
12300 10
45699 100

I've seen the ALL predicate in examples, but never used it.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Friday, January 20, 2006 6:23 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Grouping on a calculation

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






++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links








++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links