• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4462
  • Last Modified:

SQL Select Top 10 distinct recently viewed

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
askrenes
Asked:
askrenes
  • 6
  • 5
  • 3
  • +1
2 Solutions
 
SharathData EngineerCommented:
SELECT TOP 10 DISTINCT  PageVisited
  FROM YourTable
 WHERE USerID = 1000
  ORDER BY VisitDate DESC
0
 
askrenesAuthor Commented:
Can't Order By VisitDate if VisitDate isn't in the Select.  Incorrect syntax near the keyword 'DISTINCT'.
0
 
Nathan RileyFounderCommented:
It's this


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

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Nathan RileyFounderCommented:
Actually might need to drop the order by and the VisitDate in the select if it doesn't give you the expected results
0
 
askrenesAuthor Commented:
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
 
Nathan RileyFounderCommented:
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
 
askrenesAuthor Commented:
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
 
ErnariashCommented:
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
 
SharathData EngineerCommented:
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
 
askrenesAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
ErnariashCommented:
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
 
askrenesAuthor Commented:
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
 
ErnariashCommented:
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
 
SharathData EngineerCommented:
Did you check my query?
0
 
askrenesAuthor Commented:
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
 
SharathData EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now