[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Select Top 10 distinct recently viewed

Posted on 2008-11-07
17
Medium Priority
?
4,424 Views
Last Modified: 2012-05-05
I have a Table with Three columns: PageViewed, VisitDate, and UserID.

I need to select the Top 10 distinct pages that have been viewed most recently for a specific UserID

Small amount of sample data:

PageVisited      UserID      VisitDate
CompanyView.aspx?co=1000001      1000      2008-11-07 13:36:16.000
CompanyView.aspx?co=1000000      1000      2008-11-07 12:35:55.000
ContactView.aspx?ct=2000001      1000      2008-11-07 12:35:51.000
CompanyView.aspx?co=1000002      1000      2008-11-07 12:35:48.000
CompanyView.aspx?co=1000000      1000      2008-11-07 12:35:29.000
0
Comment
Question by:askrenes
  • 6
  • 5
  • 3
  • +1
17 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 22907979
SELECT TOP 10 DISTINCT  PageVisited
  FROM YourTable
 WHERE USerID = 1000
  ORDER BY VisitDate DESC
0
 

Author Comment

by:askrenes
ID: 22907997
Can't Order By VisitDate if VisitDate isn't in the Select.  Incorrect syntax near the keyword 'DISTINCT'.
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 22908026
It's this


Select Distinct top 10 PageVisited, VisitDate
From yourtable
where UserID = 1000
Order By VisitDate DESC

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 12

Expert Comment

by:Nathan Riley
ID: 22908044
Actually might need to drop the order by and the VisitDate in the select if it doesn't give you the expected results
0
 

Author Comment

by:askrenes
ID: 22908086
That doesn't give me distinct pages.  

If I drop the VisitDate in the select, I get "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."

If I drop the Order By, I don't get the most recent VisitDates.
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 22908153
right you need to drop both, now this will give you the top 10 PageVisited from your table.  That doesn't mean that they have had the most views, they are just the top 10 rows of that table.
Select Distinct top 10 PageVisited
From yourtable
where UserID = 1000

Open in new window

0
 

Author Comment

by:askrenes
ID: 22908178
But that's not what I'm looking for.

 I need to select the Top 10 distinct pages that have been viewed *MOST RECENTLY* for a specific UserID.  

Using your select statement gives me 10 distinct pages that were viewed 2 years ago.
0
 
LVL 9

Assisted Solution

by:Ernariash
Ernariash earned 2000 total points
ID: 22908205
I hope this will help you...

Select top 10 PageVisited, MAX(VisitDate) LastVisitDate, count(*) Qty 
From yourtable 
where UserID = 1000 
Group by PageVisited 
Order By MAX(VisitDate)

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 22908243
Check this also....
SELECT C.PageVisited
  FROM YourTable C
  JOIN (SELECT DISTINCT TOP 10 A.PageVisited,A.VisitDate
          FROM YourTable A
         WHERE A.UserId = 1000
         GROUP BY A.PageVisited,A.VisitDate
        HAVING A.VisitDate = (SELECT MAX(B.VisitDate) FROM YourTable B WHERE A.PageVisited = B.PageVisited AND B.UserId = A.UserId)
         ORDER BY A.VisitDate,A.PageVisited DESC) D
    ON C.PageVisited = D.PageVisited
0
 

Author Comment

by:askrenes
ID: 22908309
Sharath_123:, yours gave me the following error: "Column 'A.UserID' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause."

Ernariash was pretty close.

Here's what I ended up with.  



SELECT Top 10 PageViewed, Max(VisitDate) LastVisit 
From PageVisits 
Group By PageViewed 
Order By LastVisit Desc

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 22908343
As you want the most recently review page only, I am trying to display only one column in the query result. Do you want the LastVisit date also in your query result? Check the below query....

SELECT C.PageVisited
  FROM YourTable C
  JOIN (SELECT DISTINCT TOP 10 A.PageVisited,A.VisitDate,A.UserId
          FROM YourTable A
         WHERE A.UserId = 1000
         GROUP BY A.PageVisited,A.VisitDate,A.UserId
        HAVING A.VisitDate = (SELECT MAX(B.VisitDate) FROM YourTable B WHERE A.PageVisited = B.PageVisited AND B.UserId = A.UserId)
         ORDER BY A.VisitDate,A.PageVisited DESC) D
    ON C.PageVisited = D.PageVisited
   AND C.UserID = A.UserID
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22908517
Sorry, I am new to EE, and I am not familiar how the point system function, but the answers are basically the same based on T-SQL

The order by in a group by sentences are equivalents.
Order By MAX(VisitDate) = Order By LastVisit Desc

And the Where clause is part of the question see : have been viewed most recently for a specific UserID        Where UserID = 1000  

---askrenes: solution 
SELECT Top 10 PageViewed, Max(VisitDate) LastVisit 
From PageVisits 
Group By PageViewed 
Order By LastVisit Desc
---Ernariash:
Select top 10 PageVisited, MAX(VisitDate) LastVisitDate, count(*) Qty 
From yourtable 
where UserID = 1000 
Group by PageVisited 
Order By MAX(VisitDate)

Open in new window

0
 

Accepted Solution

by:
askrenes earned 0 total points
ID: 22908569
Ernariash, I tried to give you full points.   A moderator may have to figure it out since I used my solution, not yours.  Your solution was close, but did not sort properly.  
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22908611
Oh I see, I miss to copy the DESC on the order by... Can you close this now?
I have tested the query before posting it here with my tables, here is my query. Sorry Ernesto

 select top 10 claim_number, max(date_modified) 
 from  dbo.Claim_Procedures
 where user_name like 'YMCDONELL'
 group by claim_number
 order by max(date_modified) desc

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 22908882
Did you check my query?
0
 

Author Comment

by:askrenes
ID: 22908909
As mentioned above, Sharath_123, your query gave me the following error:

"Column 'A.UserID' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause."

Your second query was suggested after I'd requested to close the question (the first time), and as such I did not try it.

Additionally, the end solution seemed to be many times simpler than your suggestion.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22908956
I have updated my first query and gave you second query. If you get the answer for yoru question no matter from whomever, its fine. At the end, solution matters not the expert points.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 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