>I try to solve this problem already few days without success.
>I am using WI-V2.5.2.26540 Firebird 2.5 (windows 7).
>I have two tables:
>
>TABLE 1:
>DATA_SUMMARY:
>CON_ID_1 Data1
>1 A
>2 Bb
>3 Dd
>4 EE
>…
>1000 ABC
>
>TABLE 2:
>DATA_VIEW
>CON_ID_2 Data2 Data3
>1 D1 D1
>1 D1 D1
>2 D2 D2
>2 D2 D2
>2 D2 D2
>3 D3 Z3
>3 D3 Z3
>4 D4 Z4
>5 D5 Z5
>
>As a results I would like to have
>
>Results:
>CON_ID_1 Data1 CON_ID_2 Data2 Data3
>1 A 1 D1 D1
>2 Bb 2 D2 D2
>3 Dd 3 D3 Z3
>4 EE 4 D4 Z4
>…
>1000 ABC NULL NULL NULL
>
>So idea is I combine TABLE 1 and TABLE 2 based on CON_ID_1 and CON_ID_2 but in table2 I can have few identical rows with the same CON_ID_2 value.
>
>I tried to use such query
>
>SELECT * FROM DATA_SUMMARY a
>left join
>(select first 1 * from DATA_VIEW) as c on c.CON_ID_2=a.CON_ID_1
>
>unfortunetly it gives only NULL in the data_view tables part.
>
>I cannot use DISTINCT(c.CON_ID_2) option because it take 2.5 min to sort complete table (both tables have few thousand rows)
>
>On the internet I found only solutions for oracle and mysql but it doesn't for FB
>
>Any suggestions how to solve such problem?

Hi Kukiejko, sorry to hear you've already used days to solve this question.

A few thousand rows shouldn't normally take 2.5 minutes. Do you have an index for c.CON_ID_2?

From your problem description, I would say that the solution (provided you have indexes for CON_ID_1 or CON_ID_2) is as simple as:

SELECT DISTINCT a.CON_ID_1, a.Data1, c.CON_ID_2, c.Data2, c.Data3
FROM DATA_SUMMARY a
JOIN DATA_VIEW c on c.CON_ID_2=a.CON_ID_1

Now, I doubt that is the solution to your problem, but that is because I do not think you are telling us everything needed to solve it and the result of this query should be correct if the tables only contained the test data you provided. To solve your real problem (provided it is not as simple as I wrote above), please tell us the table structure including indexes and provide test data where your desired output differs from the output you would get from the select I wrote in the paragraph above.

A more likely answer to what you are wondering about would be something like (you may replace RDB$DB_KEY with the primary key of the table):

SELECT * FROM DATA_SUMMARY a
left join DATA_VIEW c on c.CON_ID_2=a.CON_ID_1
where not exists(select * from DATA_VIEW c2 where c.CON_ID_2 = c2.CON_ID_2 and c.RDB$DB_KEY > c2.RDB$DB_KEY)

but I am still not certain what your question really is, so it is rather flukey if this is the answer you're after.

Hope this either helps or results in a question that will lead to a better answer,
Set