By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,993 Members | 1,984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,993 IT Pros & Developers. It's quick & easy.

difficult select distinct query

P: n/a
Hi,

I have a table as following

aa Text1 aa, p@xxx.be, 15267
aa Text1 aa, p@xxx.be, 16598
aa Text1 aa, p@xxx.be, 17568
aa Text2 aa, p@xxx.be, 25698
aa Text3 aa, x@zzz.be, 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.


Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Tue, 24 Aug 2004 15:18:36 +0200, Perre Van Wilrijk wrote:
Hi,

I have a table as following

aa Text1 aa, p@xxx.be, 15267
aa Text1 aa, p@xxx.be, 16598
aa Text1 aa, p@xxx.be, 17568
aa Text2 aa, p@xxx.be, 25698
aa Text3 aa, x@zzz.be, 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.


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)
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.