Compare Current Line to Previous

Question

I want in a "Test" column to define the value "S" for only in the first row of a given "code" and all the others to define the value "N" and so successively for all other codes in a SELECT, I am using SQL SERVER.

ID COD VLR    DATA      TESTE
01 123 100 "06/09/2015"   S
04 123 400 "03/09/2015"   N
05 123 500 "02/09/2015"   N
02 456 200 "05/09/2015"   S
03 456 300 "04/09/2015"   N
06 789 600 "01/09/2015"   S

Note: I verified that it has the functions LAG and LEAD, but I could not do it using them.

Thank you for your attention.

Answer 1

An alternative using only ROW_NUMBER for those still using SQL Server 2008.

For the following table

CREATE TABLE Teste(id CHAR(02), cod INT, valor INT, data DATE);
INSERT INTO Teste(id, cod, valor, data) VALUES
('01', 123, 100, '2015-09-06'),
('04', 123, 400, '2015-09-03'),
('05', 123, 500, '2015-09-02'),
('02', 456, 200, '2015-09-05'),
('03', 456, 300, '2015-09-04'),
('06', 789, 600, '2015-09-01');

The next instruction

SELECT id,
       cod,
       valor,
       data,
       CASE 
          WHEN ROW_NUMBER() OVER (PARTITION BY cod ORDER BY id) = 1 THEN 'S'
          ELSE 'N'
       END AS Teste
FROM   Teste
ORDER BY 2, 1, 3

Will generate the following output

id  cod  valor      data        Teste
01  123  100        2015-09-06  S
04  123  400        2015-09-03  N
05  123  500        2015-09-02  N
02  456  200        2015-09-05  S
03  456  300        2015-09-04  N
06  789  600        2015-09-01  S

For those who use SQL Server 2012 (or newer) you can use the LAG function to get the same output. For example:

SELECT id,
       cod,
       valor,
       data,
       LAG('N', 1, 'S') OVER (PARTITION BY cod ORDER BY id) Teste
FROM   Teste
ORDER BY 2, 1, 3;

Sqlfiddle