Solved

Best way to use DISTINCT values in a SQL view as a dynamic source for another table in the DB

Posted on 2008-06-17
2
568 Views
Last Modified: 2013-12-16
Hi,

I'm not sure the best way to describe what I need but here goes.  I'm currently building a Tag Cloud functionality for a site in ASP.NET 2 with c# which displays the most popular searches on a site.  I'm logging each searchterm in a table and the language it was searched in and using a stored procedure to aggregate the top 10 search terms with the itemcount as source for the tag cloud.   As below:

SELECT DISTINCT TOP 10 SearchTerm,
            (SELECT COUNT(*)
            FROM SearchesTracking WHERE SearchTerm = s.SearchTerm AND Language = @lang) AS ItemCount
      FROM SearchesTracking s
      WHERE Language = @lang
      ORDER BY SearchTerm

I've been asked to create an admin screen wher the user can see a view of all the aggregated search terms and then  select which ones are published into the tag cloud via a check/uncheck box.  Herein lies my problem, as the tracking table I have at the moment records every search term and then I aggregate it
in a SQL query. Ideally I would create a reference table say TermStatus with 2 rows, Published and NotPublished.  A middle table with a the ID of the distinct searchterm and the TermStatus field.

But since the aggregated search terms are dynamic , this is not possible. Reading up on Views they seem difficult to implement an updateable view on this and plus I don't think it would generate a ID value!

What would be the best way to achieve this do you think?  Apologies if this is unclear.  Please ask for any clarification. Thanks very much in advance
0
Comment
Question by:indy28
2 Comments
 
LVL 6

Accepted Solution

by:
messen1975 earned 500 total points
ID: 21805315
Maybe I'm misunderstanding -- but would this fit your need?

SELECT DISTINCT TOP 10 SearchTerm,
            Count(*)
      FROM SearchesTracking s
      WHERE Language = @lang
        GROUP BY SearchTerm
      ORDER BY Count(*) Desc

This should give you the top 10 Search Terms along with the number of times that the search term was searched for.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21805880
You might also want to look into full-text indexes, which may provide faster results for things like that.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question