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