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.
| Author |
Topic |
|
svictoria
Starting Member
3 Posts |
 Posted - 2007-07-31 : 10:11:57
|
| Hi,I have the following select statement in SQL Server 2005 which I need to convert to SQL Server 2000.SQL Server 2000 do not have the ranking functions, so I can not use the statement as is and I am not quite sure how to convert it. If anyone could help me it would be appreciated.The statement is below:select dense_rank() over (order by c.action, s.effect_desc ) as Action,row_Number() over (partition by c.action order by c.effect))||' of '||to_char(count(c.effect) over (partition by c.action) as Effect, from hypotheses c, lookpup_action l, lookup_effect s where c.action = l.action_id and c.effect = s.effect_idsvictoria |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-31 : 10:32:09
|
This is NOT a SQL Server 2005 query.Double pipes and to_char function is ORACLE syntax.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
svictoria
Starting Member
3 Posts |
Posted - 2007-07-31 : 10:41:33
|
| I have to rework the statement for Oracle as well as SQL Server 2000. I posted the Oracle version in error. I meant to post the following:select dense_rank() over (order by c.action, s.effect_desc ) as Action,row_Number() over (partition by c.action order by c.effect))+' of '+ str(count(c.effect) over (partition by c.action) as Effect, from hypotheses c, lookpup_action l, lookup_effect s where c.action = l.action_id and c.effect = s.effect_idsvictoria |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-07-31 : 11:54:12
|
| Can you provide some sample data and expected results? |
|
|
|
svictoria
Starting Member
3 Posts |
Posted - 2007-07-31 : 12:31:24
|
| Thanks for looking at my problem, but I found a solution elsewhere. |
|
|
|
|
|
|
|
|
Convert Rank Select to SQL Server 2