2

I'm not sure if there's even a function for this, but lets say that you had a column like:

20
20
55
55
55
60
72
72

Is there a SQL query that would give you:

20    0
20    1
55    0
55    0
55    1
60    1
72    0
72    1

Basically, create a new column that would make all the values 0 except it makes the last value 1.

So what I have right now is:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} INT NOT NULL 
CONSTRAINT zero DEFAULT 0

And then I need a function that selects all the last values and then I can use an update table function to change tablename=1 for all of those values. If it makes a difference, I'm using SQL Server 2008.

awung
  • 21
  • 1
  • what are the other column, what is the primary key? – Fredou Sep 12 '13 at 23:53
  • Sorry, whoever designed the tables before me left out the primary key on some of them, but I think I can probably make a new column that auto increments: http://stackoverflow.com/questions/4862385/sql-server-add-auto-increment-primary-key-to-existing-table – awung Sep 13 '13 at 16:04

1 Answers1

4

You can use row_number() for this. I've assumed you have some other column you are using to define order.

select number, case when rn=1 then 1 else 0 end
from (
  select id, number, row_number() over (partition by number order by id desc) rn   
  from Test
) x
order by id, rn

Here's a SQL Fiddle.

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
  • I think you want to subtract 1 from the output of `ROW_NUMBER()` to match the OP's requirements. Also you may want to include `rn` in the outer `ORDER BY`. – Aaron Bertrand Sep 13 '13 at 02:01
  • @AaronBertrand I don't think I need to subtract 1 - note that I'm ordering by `id desc` in the `over`, vs. `id` (asc) in the outer order by.. But yes, `rn` should be in the outer `order by`, added that thanks. – Blorgbeard Sep 13 '13 at 02:18
  • Right, I thought you were just returning rn, sorry. – Aaron Bertrand Sep 13 '13 at 02:32
  • I think what you meant was `select number, case when rn=1 then 1 else 0 end from ( select id , number, row_number() over (partition by number order by id desc) rn from Test ) x order by id, rn`. I copied your code into my SQL and it had an error because you forgot to put the id into the select statement, so I copied the code from your Fiddle and it worked. – awung Sep 13 '13 at 16:16
  • @awung sorry, you're right. I added `id` to the inner select. Glad it helped you anyway :) – Blorgbeard Sep 14 '13 at 00:22