CODEWITHSUNDEEP
t-sqlauto-increment
Parmenides
Parmenides

Reputation: 137

T-SQL using CTE to autoincrement

I'm having trouble with a simple auto-increment using common table expressions. (I don't want ROW_NUMBER() because I will be using conditions for my incrementing) so here is a simplified version of my problem which still doesn't work. I get this error: "Invalid column name 'n'" when I try the following:

 WITH NumberSongPair ( n,s ) AS (
    SELECT 0 as n,SongKey as s from Songs where SongKey = 1
    UNION ALL
    SELECT 1 + n as n,SongKey as s 
    from Songs 
    WHERE n < 500 )
SELECT n,s FROM NumberSongPair
OPTION ( MAXRECURSION 500 )

Why can't it recognize 'n' as a newly a created incremented column? I'd even be happy just getting rid of the first select all together so long as I can keep incrementing a number as I select columns from a table.

If you are curious about my overall goals it's to ranks Songs.... with conditions with something like this:

 WITH Nbrs ( base, n,ctr ) AS (
    SELECT 0,0,0 UNION ALL
    SELECT 1 + base,'n' = case 
                        when (base + 1)%2=0 then ctr
                        when ctr <=20 then ctr
                        else null end,
                 'ctr' = case
                        when (base + 1)%2=0 then ctr + 1
                        else ctr end
    FROM Nbrs WHERE base < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )

EDIT...

Sorry, my explanation wasn't very good. I just wanted to add another psuedo auto-incrementing column that would sometimes go null instead of increment. Basically I'm trying something like this:

declare @songs table (songkey int, dropable bit, points int)

insert @songs values (1, 1, 1); insert @songs values (2, 1, 20);
insert @songs values (3, 1, 3); insert @songs values (3, 0, 11); 
insert @songs values (4, 0, 4); insert @songs values (6, 0, 2); 

select row_number() over(order by points desc) as RankingPosition, songkey, dropable, points 
from @Songs

But adding the following condition: 'dropable' songs lose their ranking (are null) if they don't make the top 3. This means SongKey #2 won't drop, but but #4 and #6 will. So the output would look like:

Rank is determined by points, but there are conditions.

Upvotes: 2

Views: 4084

Answers (2)

Andriy M
Andriy M

Reputation: 77707

WITH songs (songkey, dropable, points) AS (
  SELECT 1, 1,  1 UNION ALL
  SELECT 2, 1, 20 UNION ALL
  SELECT 3, 1,  3 UNION ALL
  SELECT 4, 0,  4 UNION ALL
  SELECT 5, 0, 11 UNION ALL
  SELECT 6, 0,  2
),
preliminaryRanking AS (
  SELECT
    rank1 = ROW_NUMBER() OVER (ORDER BY points DESC),
    *
  FROM songs
),
finalRanking AS (
  SELECT
    rank2 = ROW_NUMBER() OVER (
      ORDER BY
        CASE
          WHEN rank1 <= 3 OR dropable = 0 THEN rank1
          ELSE CAST(0x7FFFFFFF AS int)
        END
    ),
    *
  FROM preliminaryRanking
)
SELECT
  PositionRank = CASE WHEN rank1 <= 3 OR dropable = 0 THEN rank2 END,
  songkey, dropable, points
FROM finalRanking
ORDER BY rank1

Output:

PositionRank         songkey     dropable    points
-------------------- ----------- ----------- -----------
1                    2           1           20
2                    5           0           11
3                    4           0           4
NULL                 3           1           3
4                    6           0           2
NULL                 1           1           1

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44336

You should use rownumber, I don't understand 100 % what you want, but this is an example how you can use rownumber. The reason your corrent script doesn't work is because you haven't joined NumberSongPair and Songs under the union part. It seems you have nothing to join them on

declare @songs table (songkey int, base int, ctr int)

insert @songs values (1, 1, 1)
insert @songs values (2, 1, 1)
insert @songs values (3, 1, 1)

;WITH wrn AS (
select row_number() over(order by (select 1)) [rn], * from @Songs)
,a as
(
SELECT case 
when (base + 1)%2=0 then ctr
when ctr <=20 then ctr
else null end n,
case when (base + 1)%2=0 then ctr + 1
else ctr end CTR FROM WRN
)
SELECT n FROM a

If you want me to write a script to do what you want, provide me with some sample data, expected outcome and the logic behind it.

Upvotes: 0

Related Questions