difficult select distinct query

[Perre Van Wilrijk]

Hi,

I have a table as following

aa Text1 aa, [e-mail], 15267
aa Text1 aa, [e-mail], 16598
aa Text1 aa, [e-mail], 17568
aa Text2 aa, [e-mail], 25698
aa Text3 aa, [e-mail], 12258

I have to write a query as follows ...

SELECT DISTINCT TOP 500 fldText, fldContact, fldItemid
FROM table
WHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')

In the example you can see the table has rows in which text and contact or
double but with different itemid's. Now my employer wants me to show only 1
row when text and contact or the same. He doesn't mind which itemid I show
.... but I have to show one.

I've an idea of how to do this using a cursor and a temporary table but I
guess that will be fatal for the performance because then I have to loop
through all selected rows, check each row with all other rows and store the
primary key in the temporary table if dedected it isn't double. Afterwards
I can execute ... SELECT ... FROM TABLE where primary key in (select
temp_primarykey from #temptable).

I hoped I could do everything in 1 "easy" SELECT but I should not know how?
Any ideas are much appreciated.

Thanks a lot.
Perre Van Wilrijk.

[Hugo Kornelis]

On Tue, 24 Aug 2004 15:18:36 +0200, Perre Van Wilrijk wrote:

Hi,

I have a table as following

aa Text1 aa, [e-mail], 15267
aa Text1 aa, [e-mail], 16598
aa Text1 aa, [e-mail], 17568
aa Text2 aa, [e-mail], 25698
aa Text3 aa, [e-mail], 12258

I have to write a query as follows ...

SELECT DISTINCT TOP 500 fldText, fldContact, fldItemid
FROM table
WHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')

In the example you can see the table has rows in which text and contact or
double but with different itemid's. Now my employer wants me to show only 1
row when text and contact or the same. He doesn't mind which itemid I show
... but I have to show one.

I've an idea of how to do this using a cursor and a temporary table but I
guess that will be fatal for the performance because then I have to loop
through all selected rows, check each row with all other rows and store the
primary key in the temporary table if dedected it isn't double. Afterwards
I can execute ... SELECT ... FROM TABLE where primary key in (select
temp_primaryke y from #temptable).

I hoped I could do everything in 1 "easy" SELECT but I should not know how?
Any ideas are much appreciated.

Thanks a lot.
Perre Van Wilrijk.

Hi Perre,

Try it with this instead:

SELECT fldText, fldContact, MIN(fldItemid)
FROM table
WHERE fldCat = 10
AND CONTAINS (fldText, 'Text1')
GROUP BY fldText, fldContact
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)