Re: sql question - distinct

soalvajavab1_at_yahoo.com schrieb:
> The result of the following query is what I like to get:
>
> SELECT MAX(BFP_NUMBER), MAX(METER_NUMBER), UNIT_ID
> FROM BMA_RENTAL_AGREEMENT_UPPER
> GROUP BY UNIT_ID
>
> but I do not need/want to have a group by FUNCTION MAX or any other
> group by function
>
> Is this possible? to do it in other way?
>
>
>
> Steve Howard wrote:

>> soalvajavab1_at_yahoo.com wrote:
>>> I want DISTINCT only applies to UNIT_ID but I also have data for
>>> BFP_NUMBER, METER_NUMBER  in each result rows.
>>>
>> I'm still not clear on what you want.  Using Galen's example above, do
>> you want to see...
>>
>> UNIT_ID  BFP_NUMBER  METER_NUMBER
>> 1        10          100
>>           11          101
>> 2        12          102 
>>
>> ?
>>
>> Regards,
>>
>> Steve

You can give this stupid SQL-command a chance:

select t1.unit_id,
(

   select max( t2.numberField )
   from table_name t2
   where t1.unit_id = t2.unit_id
) as numberField
from
(

   select distinct unit_id
   from table_name
) t1;

Please do not answer that you do not want subqueries.

If you do not accept the means provided by your SQL database, try Microsoft Works or Excel and use VBA or something like that.

Hope this helps

Martin

-- 
Firma/Company:                                              CRESD GmbH
Phone: +49-89-65 30 95 63                      Fax: +49-89-65 30 95 64
WWW:                                               http://www.cresd.de
S-Mail:                                Freibadstr. 14, D-81543 München
PGP-Key:                            http://www.cresd.de/edv/pgpkey.txt
Open BC (Einladung)            http://www.openbc.com/go/invita/4561755