One more time - advanced search :-)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

One more time - advanced search :-)

Author

Topic

medtech26
Posting Yak Master

169 Posts

Posted - 2009-07-20 : 22:01:09

Greetings all,

I have a search function online that brings all related concepts to each and every word in submitted text. Here's how it works:

1. Concepts table with id and term (holding about 30K concepts with unique id/key).
2. Synonyms table with id and term (holding synonyms that related to concepts and sharing the same id, id is indexed).
3. Word table with id and term (holding a word breakdown for concepts and synonyms, no word duplication per concept id, id and word are indexed separately and combined using as the key).

I'm searching for each word in the word table by it's indexed term and bring all related concepts (grouped), so far all works as planned. My problem is that I don't know how to order the results as follow:
Fist: exact match
Second: matches with the same word(s) exist in the concept table
Third: matches for synonyms

Any idea how to approach this?

Using SQL Server 2005

Please let me know if the question is unclear so I can clarify further, thanks.

medtech26
Posting Yak Master

169 Posts

Posted - 2009-07-22 : 14:13:19

I guess the question wasn't clear enough, any way, next question (less complicated :-P).

For some reason SQL Server ignores "HAVING" statement when using UNION query and brings all records, here's my (sample) query:


SELECT email, NumOccurrences FROM (
  SELECT email, COUNT(email) AS NumOccurrences 
  FROM users 
  GROUP BY email 
UNION ALL 
  SELECT email, COUNT(email) AS NumOccurrences 
  FROM new_users 
  GROUP BY email 
) T1 
GROUP BY email 
HAVING NumOccurrences=MAX(NumOccurrences) 
ORDER BY NumOccurrences DESC, email

I also tried to include the HAVING statement in the initial queries (inside the union) but got an error message ("Cannot perform an aggregate function on an expression containing an aggregate or a subquery.").

Any idea what's going on?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 14:28:51

[code]
WITH cte (email, NumOccurrences)
AS (
SELECT email, COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
UNION ALL
SELECT email, COUNT(email) AS NumOccurrences
FROM new_users
GROUP BY email
)
SELECT * FROM cte WHERE NumOccurrences = (SELECT max(NumOccurrences) FROM cte)
[/code]

edit -- whoops left out a parentheses...fixed

medtech26
Posting Yak Master

169 Posts

Posted - 2009-07-22 : 14:36:18

Thanks, it works!

However, I wander what's wrong with having the HAVING in my sample?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 14:41:19

can't assign an aggregate that way

HAVING NumOccurrences=MAX(NumOccurrences)

medtech26
Posting Yak Master

169 Posts

Posted - 2009-07-22 : 14:51:39

For some reason your solution is slower, I have to refine it somehow.

Edit: slower then not having a "filter" at all.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 16:03:03

[code]
SELECT email, COUNT(email) AS NumOccurrences
into #t
FROM users
GROUP BY
email
UNION ALL
SELECT email, COUNT(email) AS NumOccurrences
FROM new_users
GROUP BY
email

SELECT t.*
FROM #t t
JOIN (
SELECT max(NumOccurrences) maxOccur
FROM #t
) x
On x.maxOccur = t.NumOccurrences

DROP TABLE #t
[/code]

medtech26
Posting Yak Master

169 Posts

Posted - 2009-07-22 : 17:18:19

Thanks, this works fine and fast as well.

Where does #t created\saved? I looked for it by browsing tables and views using the management studio (after removing the drop statement from the query) and could not locate it. I'm using a similar query on my site and wandered if I should replace #t with a dynamic name that's different for each user (obviously, with the drop statement ;-))?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 21:32:52

in tempdb. it's a temporary table.

medtech26
Posting Yak Master

169 Posts

Posted - 2009-07-23 : 14:27:06

Thank you very much for your time and suggestions, the problem is completely solved and works great. I've also found this article ([url]http://www.sqlteam.com/article/temporary-tables[/url]) that answered most of my questions\concerns regarding temp tables and might me helpful for others :-).

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 14:47:48

You're very welcome :) Glad to hear it's all working.

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 06:28:04

The ways to avoid this error is simple

just look into my post

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources