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.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767