Re: remove dups
> I can see what you are trying with the SELECT FIRST, but you need the
> FIRST of each group. So I opted for a stored procedure. Here is my test
> case:
Hi Mark,
Thank you very much for your reply.
Now let me go a bit deeper with -944 error:
I have two tables with many-to-many relationship, and I need to use just
the first row from the second table. Example:
primary table:
id amount
1 20
1 30
2 10
3 20
3 40
secondary table:
id text
1 one
1 oonnee
2 two
2 ttwwoo
desired result:
id amount text
1 20 one
1 30 one
2 10 two
3 20
3 40
(I don't care if the text is "two" or "ttwwoo", as long as it's always
the same text. Let's say that I want the shortest text from the
secondary table.)
(I know that such relations should not be needed in good database
design. Unfortunately, one of the tables if from an external source that
I cannot influence)
Since I couldn't figure a suitable select, I decided to simplify the
problem: I added the "text" column to the structure of primary, and I
wanted to use UPDATE:
update primary
set text=
(select first 1 text from secondary
where primary.id=secondary.id)
... This gives error 944: Unknown error
Okay, I did not order secondary. So:
update primary
set text=
(select first 1 text from secondary
where primary.id=secondary.id order by text)
... Syntax error at the start of "order by".
But I'm sure that SELECT FIRST per se works fine, because:
select first 1 id,text from secondary where id="1"
...is OK.
What's the problem?
--
Milos Prudek