Ordering content by usage - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Ordering content by usage

Author

Topic

dcobb
Yak Posting Veteran

76 Posts

Posted - 2002-03-12 : 11:37:34

Background:

I have one table with one column (log_uristem) - taken from IIS LOG

Contents of log_uristem...

/list_categories.asp
/index.asp
/list_categories.asp
/otherpage.asp
/andanother.asp
/index.asp
/list_categories.asp

What I need to be able to do is sort all contents (both ways: ASC & DESC) based on occurance, e.g. what was the most accessed, what was the least accessed AND remove all duplicates.

So the output would be for DESC:

/list_categories.asp
/index.asp
/otherpage.asp
/andanother.asp

Any ideas?

Thanks

dC

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-03-12 : 11:45:45

You Need GROUP BY. You want to group all of these URL's together, and then count the number of each you have right?

CREATE TABLE #URL(log_uristem varchar(100))

INSERT INTO #URL(log_uristem) values('/list_categories.asp')
INSERT INTO #URL(log_uristem) values('/index.asp')
INSERT INTO #URL(log_uristem) values('/otherpage.asp')
INSERT INTO #URL(log_uristem) values('/andanother.asp')
INSERT INTO #URL(log_uristem) values('/index.asp')
INSERT INTO #URL(log_uristem) values('/list_categories.asp')
INSERT INTO #URL(log_uristem) values('/list_categories.asp')
INSERT INTO #URL(log_uristem) values('/list_categories.asp')
INSERT INTO #URL(log_uristem) values('/otherpage.asp')

SELECT log_uristem, Count(*) as NumOfOccurances FROM #URL GROUP BY log_uristem ORDER BY NumOfOccurances
SELECT log_uristem, Count(*) as NumOfOccurances FROM #URL GROUP BY log_uristem ORDER BY NumOfOccurances DESC

DROP TABLE #URL

Michael

dcobb
Yak Posting Veteran

76 Posts

Posted - 2002-03-12 : 15:21:36

Sounds good to me.

Thank you.

dC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources