[SOLVED] conditional row_number

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)

[SOLVED] conditional row_number

Author

Topic

ThePrisoner
Starting Member

18 Posts

Posted - 2007-04-03 : 10:08:20

hello

i have the following expression
ROW_NUMBER() OVER (ORDER BY SUM(Units) DESC)

i would like to limit the ranking to the value of a time period field, which has 2 possible values in the table and have 2 ranking fields each for one time period :

CURRENTRANK : if timeperiod = 2007, then use record in the ranking
PREVIOUSRANK : if timeperiod = 2006, then use record in the ranking

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 10:18:24

ROW_NUMBER() OVER (PARTITION BY TimePeriod ORDER BY Units DESC)

Peter Larsson
Helsingborg, Sweden

ThePrisoner
Starting Member

18 Posts

Posted - 2007-04-03 : 10:41:06

thanks for the quick answer, unfortunately, I didn't explain enough :

my query looks like this :
select region,totalunits,currentrank expression,prevrank expression
from mytable
group by region

so the period field is not in the output, it is just used in the expressions.
for example I have 2 expressions for TotalUnits:
sum(case when (x.period = @period) then units else 0 end) as TotalUnits ,
sum(case when (x.period = @refperiod) then units else 0 end) as PrevTotalUnits ,

I solved it by using a CTE
thanks anyway

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 10:59:15

Great! Thank you for updating the topic too!
But... What has the topic title with the solution to do?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources