How to find distinct top X values?

Question

How to find distinct top X values?

Pete T-366679

SSCrazy

Points: 2065
More actions
December 6, 2007 at 10:33 am

#63125

I'm sure this is simple but I'm just not getting it.....can someone help me?
I have a log table that logs a row every time an item is hit, and the datetime that it was accessed. I want to create a Most Recently Viewed list based on this data.
Essentially I want the 5 most recent *different* items that were accessed. My table has username, itemid, and dateaccessed. I want something like this:
select distinct top 5
itemid
from tblhits
where username='testusername'
order by dateaccessed desc
This of course doesn't work because dateaccessed isn't part of the distinct query. How can I do this? Thanks!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 1

You can use a subquery or a CTE. Either one will do the same thing, but the CTE is more readable if you are using SQL Server 2005.

For instance:

; with CTE1 as (

select distinct top 5

itemid, dateaccessed

from tblhits

where username='testusername'

order by dateaccessed desc

)

select itemid

from CTE1

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 2

Doh, I'm on SQL2000 for a month or so longer until we finish the upgrade to 2005....what's the CTE again?

The subquery seems to work, it just takes longer than what I'd prefer. Maybe that's the best I can do, I'll have to run the analyzer and see if I can optimize it some. Is there no better way of doing this?

Thanks again for your help!

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 3

CTE (Common Table Expressions) were introduced in 2005 and can make certain things easier.

I cannot think of any way to speed it up immediately.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

Or something like this?

SELECTTOP 5 T.*

FROM( SELECT itemid, MAX( dateaccessed ) AS SNO FROM tblhits WHERE username = 'testusername' GROUP BY itemid ) T

ORDER BY SNO DESC

--Ramesh

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 5

Ramesh (12/6/2007)
Or something like this?
SELECTTOP 5 T.*
FROM( SELECT itemid, MAX( dateaccessed ) AS SNO FROM tblhits WHERE username = 'testusername' GROUP BY itemid ) T
ORDER BY SNO DESC

Thank you, this seems to work great!