Ranking function problem - 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 2008 Forums

Transact-SQL (2008)

Ranking function problem

Author

Topic

omnisysgroup
Starting Member

10 Posts

Posted - 2011-08-30 : 16:08:10

I am trying to get my output to look like this.
I cant seem to get any ranking function to get my results like this.

148447 I 2010-11-10 1
148447 I 2011-08-05 1
148447 A 2011-08-18 2
170443 A 2011-08-06 1
170443 A 2011-08-09 1

create table tmp (cme_uid int, cme_status char(1), cme_eff_date date)
insert into tmp
select 148447,'I','11/10/10' union
select 148447,'I','8/5/11' union
select 148447,'A','8/18/11' union
select 170443,'A','8/6/11' union
select 170443,'A','8/9/11'

X002548
Not Just a Number

15586 Posts

Posted - 2011-08-30 : 16:10:47

I don't get it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

omnisysgroup
Starting Member

10 Posts

Posted - 2011-08-30 : 16:15:00

The last column in the output is the rank. I need to rank the same status for the same id and then increment based on that.

X002548
Not Just a Number

15586 Posts

Posted - 2011-08-30 : 16:25:23

ok, but your sample code doesn't do that

should it be 1,2,3,1,2????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

omnisysgroup
Starting Member

10 Posts

Posted - 2011-08-30 : 16:29:26

I need a query to return the results I posted, but I cant seem to get one to work.

omnisysgroup
Starting Member

10 Posts

Posted - 2011-08-30 : 16:35:33

Thanks all but I found my answer.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-08-30 : 16:42:47

Would it be impertinent to ask to see the solution?

=======================================
The first rule of Tautology Club is the first rule of Tautology Club. -xkcd, (Honor Societies)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources