Please,

Place primary keys and data examples (one record for ITEMS and some records
for DETAILS)!

2011/6/4 Marcin Bury <mbury@...>

> 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.
>
> Is it possible at all not using procedures or 'execute block' methods?
> Maybe CTE might be helpfull here? But CTE is quite new feature for me
> and I don't know how to start using it...
>
> Thanks in advance for any clues
>
> 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
>
>
>
>

[Non-text portions of this message have been removed]