Select Most Active User Download Query

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)

Select Most Active User Download Query

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2006-04-11 : 12:58:02

Im trying to construct a query which is sorted to show users who have downloaded the most articles first. My table structure is:

iditem, articlename, idarticle, iduser, downloaddate

How would I write a query to show me article download history displaying: iduser, articlename, datedownloaded

and sorted by the iduser with the most downloads...

thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-11 : 13:02:18

Somthing like this

Select Count(1) As Download,IdUser,IdArticle From Tbl
Group by IdUser,IdArticle
order by Iduser

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-11 : 13:07:06

--data
declare @t table (iditem int identity(1, 1), articlename varchar(10), idarticle int, iduser int, downloaddate datetime)
insert @t
          select 'a', 1, 1, '1 jan 2006'
union all select 'a', 1, 3, '2 jan 2006'
union all select 'b', 2, 1, '3 jan 2006'
union all select 'c', 3, 1, '4 jan 2006'
union all select 'c', 3, 2, '5 jan 2006'
union all select 'd', 4, 3, '6 jan 2006'
union all select 'd', 4, 1, '7 jan 2006'

--calculation
select a.iduser, a.articlename, a.downloaddate from @t a
    inner join (select iduser, count(*) as count from @t group by iduser) b on a.iduser = b.iduser
order by b.count desc, articlename

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-11 : 13:10:03

U want to sort by the # of articles downloaded --> each day ?
Give some sample data and expected results

Srinika

ljp099
Yak Posting Veteran

79 Posts

Posted - 2006-04-11 : 14:11:49

In my original example I didnt include all the tables Im actually joining for ease of example. However, I cant get the sql statements you describe to run with the Select statement in the Inner Join and using the group by clause properly. Here is my current statement (Im joining 4 tables, Users, Documents, UserDownloadHistory, Organizations):

SELECT O.orgName, REPLACE(CONVERT(CHAR(10),
O.licExpirationDate, 110), '-', '/') AS LicExpDate,
REPLACE(CONVERT(CHAR(10), O.registrationDate, 110),
'-', '/') AS RegDate, U.idUser,U.NoUsers, UDH.idItem, UDH.articleDownloaded,
REPLACE(CONVERT(CHAR(10), udh.downloadDateTime, 110),
'-', '/') AS DownloadDate, UDH.articleDownloadedID, UDH.uniqueUserID,Documents.Title

FROM Org AS O

INNER JOIN
Users AS u ON O.idOrg = U.idOrg

INNER JOIN
UserDownloadHistory AS UDH ON U.NoUsers = UDH.uniqueUserID

INNER JOIN
Documents ON UDH.articleDownloadedID = Documents.DocumentID

WHERE
(UDH.downloadDateTime >= @FromDate) AND (UDH.downloadDateTime <= @ToDate)

ORDER BY UDH.downloadDateTime DESC

Thanks for your help

ljp099
Yak Posting Veteran

79 Posts

Posted - 2006-04-11 : 15:05:48

I want to show the most downloaded articles for a date range (selected by the user). The most downloaded articles would simply be displayed first in the results. Heres a single row of data, i just need to get the sort to show users who have downloaded the most articles first:

Org Name Download Date Document Title Document File Name IDUser
B 04/11/2006 Best Practices... zant_3_13.pdf premenko

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-04-13 : 08:57:12

What about (not checked for typos):

SELECT O.orgName, REPLACE(CONVERT(CHAR(10),
O.licExpirationDate, 110), '-', '/') AS LicExpDate,
REPLACE(CONVERT(CHAR(10), O.registrationDate, 110),
'-', '/') AS RegDate, U.idUser,U.NoUsers, UDH.idItem, UDH.articleDownloaded,
REPLACE(CONVERT(CHAR(10), udh.downloadDateTime, 110),
'-', '/') AS DownloadDate, UDH.articleDownloadedID, UDH.uniqueUserID,Documents.Title
FROM Org AS O

INNER JOIN
Users AS u ON O.idOrg = U.idOrg

INNER JOIN
(SELECT uniqueUserID, COUNT(*) AS Rank
FROM UserDownloadHistory
WHERE (downloadDateTime >= @FromDate) AND (downloadDateTime <= @ToDate)
GROUP BY uniqueUserID) AS DTbl ON DTbl.uniqueUserID = U.NoUsers

INNER JOIN
UserDownloadHistory AS UDH ON U.NoUsers = UDH.uniqueUserID AND (UDH.downloadDateTime >= @FromDate) AND (UDH.downloadDateTime <= @ToDate)

INNER JOIN
Documents ON UDH.articleDownloadedID = Documents.DocumentID

ORDER BY DTbl.Rank DESC, UDH.uniqueUserID, UDH.downloadDateTime DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources