CODEWITHSUNDEEP
sqlsql-servert-sql
moons
moons

Reputation: 209

T-SQL Statement OVER Clause Ranking Functions

I have a table which looks like this:

Field1  Field2  ValidFrom
200     a       01.01.1999
200     b       01.01.2015
210     c       01.01.2015
210     c       01.01.2010

Now I try to generate a select-statement with an additional column, that increments when Field1 stays the same, but Field2 changes. The order of the value in the extraColumn should depend on the ValidFrom, which means e.g. 1 for 01.01.1999 and 2 for 01.01.2015 (and not the other way around!). When Field1 changes, the value in the extraColumn should start with 1 again. It should keep the same value, when the combination of Field1 and Field2 doesn't change. So the result I'd like to have would look like this:

extraColumn Field1  Field2  ValidFrom
1           200     a       01.01.1999
2           200     b       01.01.2015
1           210     c       01.01.2015
1           210     c       01.01.2010

I tried to get this result by using this query with the RANK() function:

select RANK() OVER (
  PARTITION BY [Field1], [Field2] 
  ORDER BY [ValidFrom] DESC
) as 'extraColumn'
,Field1 ,Field2 ,ValidFrom
FROM table1

Unfortunately this did not work as I expected it to and it did kind of the opposite of which I wanted, so my result looked like:

extraColumn Field1  Field2  ValidFrom
1           200     a       01.01.1999
1           200     b       01.01.2015
1           210     c       01.01.2015
2           210     c       01.01.2010

Any ideas what I did wrong?

Upvotes: 2

Views: 234

Answers (4)

Roger Wolf
Roger Wolf

Reputation: 7692

Here I have answered a similar problem. Below is the adaptation to your conditions:

-- Preparation
declare @t table (
  Field1 int,
  Field2 char,
  ValidFrom date
);

insert into @t (Field1, Field2, ValidFrom)
values
(200, 'a', '19990101'),
(200, 'b', '20150101'),
(210, 'c', '20150101'),
(210, 'c', '20100101');

-- The query
with cte as (
  select t.*,
    lag(t.Field2) over(partition by t.Field1 order by t.ValidFrom) as [Prev2]
  from @t t
)
select c.Field1, c.Field2, c.ValidFrom,
  sum(case when c.Prev2 = c.Field2 then 0 else 1 end)
  over(partition by c.Field1 order by c.ValidFrom) as [ExtraColumn]
from cte c;

I only hope you aren't going to run this against millions or records, because 2 partitionings won't make it easy on the CPU and memory. Oh yes, and you need SQL Server 2012 or later for this to work.

Upvotes: 2

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

I think you will have to use recursion here to calculate row-by-row whatever field2 changed or not.

WITH CTE_RN AS 
(
    SELECT  * 
    , ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY ValidFrom) RN
    FROM Table1
)
, RCTE AS 
(
    SELECT *, 1 AS ExtraColumn 
    FROM CTE_RN WHERE RN = 1

    UNION ALL

    SELECT c.*
    , CASE WHEN r.Field2 = c.Field2 THEN r.ExtraColumn ELSE r.ExtraColumn + 1 END
    FROM RCTE r 
    INNER JOIN CTE_RN c ON r.Field1 = c.Field1 AND r.RN + 1 = c.RN

)
SELECT * 
FROM RCTE
ORDER BY Field1, ValidFrom
OPTION (MAXRECURSION 0)

SQLFiddle DEMO (I added few more rows for more complex sample)

Upvotes: 1

Jason A. Long
Jason A. Long

Reputation: 4442

Try it like this...

WITH 
    cte_MaxDate AS (
        SELECT 
            *,
            MaxDate = MAX(td.ValidFrom) OVER (PARTITION BY td.Feild1, td.Field2)
        FROM
            #TestData td
        )
SELECT 
    DENSE_RANK() OVER (PARTITION BY md.Feild1 ORDER BY md.MaxDate, md.Field2),
    md.Feild1, md.Field2, md.ValidFrom--, md.MaxDate
FROM
    cte_MaxDate md;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Use DENSE_RANK, you need to use Field2 in order by to get the required result not ValidFrom

DENSE_RANK() OVER (PARTITION BY [Field1] ORDER BY [Field2])

Upvotes: 2

Related Questions