Returning Ranked Results - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Returning Ranked Results

Author

Topic

richardo000
Starting Member

1 Post

Posted - 2008-02-08 : 16:45:58

Hi Everyone,

I need some help from a SQL guru! I have a table that consists of 10,000's of records. I need to return the most recent entry by time and code for that day. I know I can get what I want using MS SQL 2005 ROW_NUMBER OVER() but I have no idea how I can do this in MS SQL 2000 e.g.

DATA:
2008-02-01 10:00AM ASX1
2008-02-01 10:30AM ASX1
2008-02-02 10:00AM ASX1
2008-02-02 10:30AM ASX1
2008-02-03 10:00AM ASX1
2008-02-03 10:30AM ASX1
2008-02-04 10:00AM ASX2
2008-02-04 10:30AM ASX2

EXPECTED RESULTS FOR ASX1:
2008-02-03 10:30AM ASX1
2008-02-02 10:30AM ASX1
2008-02-01 10:30AM ASX1

I would like to rank each result in a nested query and then only select the top n.

Any help or a solution would be much appreciated!

Thanks in advance!

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-08 : 16:55:11

Have you tried using RANK() in place of ROW_NUMBER() with the same OVER() statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 23:08:27

Try this:-

SELECT tmp.DateField,tmp.TextField
FROM
(
SELECT DateField,
       TextField,
       (SELECT COUNT(*) + 1 
        FROM Table 
        WHERE TextField=t.TextField
        AND DATEADD(d,DATEDIFF(d,0,DateField),0)= DATEADD(d,DATEDIFF(d,0,t.DateField),0)
        AND DateField>t.DateField) AS RowNo
FROM Table t
)tmp
WHERE tmp.RowNo=1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-09 : 01:51:03

or

select max(DateField),TextField from Table
group by dateadd(day,datediff(day,0,DateField),0),TextField

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources