How to select a different rank by day

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 2005 Forums

Transact-SQL (2005)

How to select a different rank by day

Author

Topic

Rul3R
Starting Member

1 Post

Posted - 2009-05-10 : 16:56:58

Hi

i have this query:

SELECT title, subtitle, publication_date
,RANK() OVER (ORDER BY credits) AS 'Rank'
FROM pressreleases where publication_date > getdate()

and it returns for example:

Title | Subtitle | Publication_date | Rank
test1 test1 11-05-2009 1
test2 test2 11-05-2009 2
test3 test3 12-05-2009 3
test4 test4 12-05-2009 4

But what i need is to get the rank by day so it must be like this:

Title | Subtitle | Publication_date | Rank
test1 test1 11-05-2009 1
test2 test2 11-05-2009 2
test3 test3 12-05-2009 1
test4 test4 12-05-2009 2

Thank you!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-10 : 17:08:00

Use partition by ... to say where it has to start over with value 1

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-10 : 17:09:34

And look here for more Information:
http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-10 : 20:55:56

I think you might want row #, not rank


SELECT title, subtitle, publication_date
,Row_Number() OVER (partition by dateadd(dd,0, datediff(dd,0,PublicationDate)) ORDER BY credits) AS 'Rank'
FROM pressreleases where publication_date > getdate()

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources