help needed w/ query - 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

General SQL Server Forums

New to SQL Server Programming

help needed w/ query

Author

Topic

dh_anu
Starting Member

2 Posts

Posted - 2009-05-11 : 03:07:09

I have a table which has
url_id, emp_id and timestamp when it was bookmarked by that emp.

I need to get the latest 20 distinct urls that got bookmarked.

I've tried all possible combinations of distinct and group by.. but am not getting the correct results.

i need the distinct url_ids... but i need them ordered by the timestamp and since the amount of data is huuuuuuuge, i need to fetch only a pageful of rows at a time.

any help would be appreciated.

thanks so much in advance.
Dh_anu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 03:11:02

[code]
select top 20 *
from
(
select url_id, timestamp = max(timestamp)
from table
group by url_id
) u
order by timestamp desc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dh_anu
Starting Member

2 Posts

Posted - 2009-05-11 : 04:11:21

thanks KH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 10:40:45

if sql 2005


SELECT TOP 20 url_id, emp_id,timestamp 
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY url_id ORDER BY [timestamp]  DESC) AS Seq,
url_id, emp_id,[timestamp] 
FROM Table
)t
WHERE Seq=1
ORDER BY timestamp  DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources