Just to let everybody know

it works even better than I expected

Thank you, Norman

Marcin

W dniu 04.06.2011 19:47, Marcin Bury pisze:

> Hello Norman
>
> W dniu 04.06.2011 18:26, Norman Dunbar pisze:
>> Hi Marcin,
>>
>>
>> On 04/06/11 15:58, Marcin Bury wrote:
>>> Hello All
>>>
>>> I have two tables in master-detail relation:
>>> ITEMS
>>> Item_ID
>>> Item_Name
>>> Item_Desc
>>> Item_EntryDate
>>>
>>> DETAILS
>>> ID
>>> Item_ID
>>> Some_Code_1
>>> Some_Code_2
>>> Some_Integer_Value
>>>
>>> I would like to query both DETAILS and ITEMS tables like this:
>>>
>>> SELECT I.Item_ID, I.Item_Name,
>>> D.Some_Code_1, D.Some_Code_2, D.Some_Integer_Value
>>> From DETAILS D
>>> Left Join ITEMS I ON (I.ITEM_ID = D.ITEM_ID)
>>> where I.Item_EntryDate between :D1 and :D2
>>>
>>> but here is the most important thing:
>>>
>>> the query should return only one DETAIL record for each Item_ID - this
>>> one with the highest value of Some_Integer_Value column.
>>
>> Off the top of my head, and without any testing at all, something like
>> the following, might work, if I've understood you correctly:
>>
>> with max_integer_value as (
>> select item_id,
>> some_code_1,
>> some_code_2,
>> max(some_integer_value) as max_int
>> from details
>> group by item_id,
>> some_code_1,
>> some_code_2
>> )
>> select i.item_id,
>> i.item_name,
>> d.some_code_1,
>> d.some_code_2,
>> d.max_int
>> from max_integer_value d
>> join items i on (i.item_id = d.item_id)
>> where i.item_entrydate between something and something_else
>> order by<whatever you like here>;
>>
>> As I said, I have not tried it. It looks ok to me though, testing will tell.
>>
>> It does assume that you get the same data in the item_id, some_code_1,
>> some_code_2 of the details table, and only the integer value differs.
>>
>> Cheers,
>> Norm.
>>
>
> Some_Code_1 and Some_Code_2 actually can be different, but these from
> the highest some_integer_value are most significant.
> I will try this on real tables and data and let you all know how it works.
>
> Thank you very much
> Marcin
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org 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
>
>
>
>