Compare Current Line with Previous [duplicate]

Question

Compare Current Line with Previous [duplicate]

Navigation

#1 by (3 votes)

3

I want in a "Test" column to set the "S" value to only the first line of a given "code" and all others set the value "N" and so on to all other codes in a SELECT, I'm 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 have checked LAG and LEAD functions, but I could not do that using them.

Thanks in advance for your attention.

sql sql-server

asked by anonymous 16.09.2015 / 21:28

1 answer

How to print a dotted decimal number, not comma? Why should E.printStackTrace be removed?

score 3 · Accepted Answer

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

To 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 following statement

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

It 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 using 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