Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

Here is my table

ID      Field1  Field2      Field3      Field5  Field7
357     357     2013-03-07  08:02:02:275    t02 bBCD00103RG
365     365     2013-03-07  08:02:05:307    t02 bR U00103w
374     374     2013-03-07  08:02:08:322    t02 bR U00103w
474     474     2013-03-07  08:02:41:307    t02 bR U00103w
1378    1378    2013-03-07  02:25:45:447    t02 bR U00103w
1381    1381    2013-03-07  02:25:46:416    t02 bBFU3
1386    1386    2013-03-07  02:25:49:057    t02 bBFU02405LL
1394    1394    2013-03-07  02:25:52:260    t02 bBFU02405LL
1504    1504    2013-03-07  02:26:42:307    t02 bBFU02405LL
1510    1510    2013-03-07  02:26:45:275    t02 bBFU02405LL
1516    1516    2013-03-07  02:26:48:307    t02 bBFP02405LI
1523    1523    2013-03-07  02:26:52:088    t02 bBFP02405LI
1530    1530    2013-03-07  02:26:54:885    t02 bBFP02405LI
1556    1556    2013-03-07  02:27:06:307    t02 bBFP02405LI
1562    1562    2013-03-07  02:27:09:307    t02 bBFP02405LI
1568    1568    2013-03-07  02:27:12:307    t02 bR L02405o
1574    1574    2013-03-07  02:27:15:338    t02 bBCL/
1580    1580    2013-03-07  02:27:18:635    t02 bBCL00103RO
1587    1587    2013-03-07  02:27:21:307    t02 bBCL00103RO
1714    1714    2013-03-07  02:28:21:291    t02 bBCD00103RG
1721    1721    2013-03-07  02:28:24:291    t02 bBCD00103RG
1728    1728    2013-03-07  02:28:27:338    t02 bBCD00103RG
1734    1734    2013-03-07  02:28:30:291    t02 bBCD00103RG
1740    1740    2013-03-07  02:28:33:447    t02 bR U00103w
1996    1996    2013-03-07  02:30:33:291    t02 bR U00103w

The end result must looκ like this

ID      Field1  Field2      Field3      Field5  Field7
357     357     2013-03-07  08:02:02:275    t02 bBCD00103RG
365     36 5    2013-03-07  08:02:05:307    t02 bR U00103w
1381    1381    2013-03-07  02:25:46:416    t02 bBFU3
1386    1386    2013-03-07  02:25:49:057    t02 bBFU02405LL
1516    1516    2013-03-07  02:26:48:307    t02 bBFP02405LI
1568    1568    2013-03-07  02:27:12:307    t02 bR L02405o
1574    1574    2013-03-07  02:27:15:338    t02 bBCL/
1580    1580    2013-03-07  02:27:18:635    t02 bBCL00103RO
1714    1714    2013-03-07  02:28:21:291    t02 bBCD00103RG
1740    1740    2013-03-07  02:28:33:447    t02 bR U00103w

Ι want the first occurence of the value of Field7, Ι want to compare the value previous value of Field7 and if it is different then only output.

share|improve this question

marked as duplicate by Jon Seigel, Max Vernon, bluefeet, RolandoMySQLDBA, Paul White Oct 3 '13 at 3:29

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1  
Can you tell us what you mean by "first occurence[sic] of the value of Field7"? That might then help us to understand how to best compare that value to something considered to be previous. –  SQLRockstar Oct 2 '13 at 13:10
1  
What version of SQL Server are you using? –  Max Vernon Oct 2 '13 at 14:33
    
This is a variation of the "island" grouping problem. This has been asked before, but I can't find the original question. –  Jon Seigel Oct 2 '13 at 15:10
1  
@Max: That wasn't the one I remember, but it will do. Thanks. –  Jon Seigel Oct 2 '13 at 16:36
add comment

2 Answers 2

SQL Server 2012 offers the 'LAG' keyword which enables a SELECT statement to display the previous row. You could use this to compare the current row to the previous row in the WHERE clause.

Here is an example:

USE Test;
CREATE TABLE LagTest
(
    ID INT NOT NULL CONSTRAINT PK_LagTest PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Data NVARCHAR(255) NOT NULL 
);

INSERT INTO LagTest (Data) VALUES ('test1');
INSERT INTO LagTest (Data) VALUES ('test1');
INSERT INTO LagTest (Data) VALUES ('test1');
INSERT INTO LagTest (Data) VALUES ('test2');
INSERT INTO LagTest (Data) VALUES ('test2');
INSERT INTO LagTest (Data) VALUES ('test2');
INSERT INTO LagTest (Data) VALUES ('test3');
INSERT INTO LagTest (Data) VALUES ('test3');
INSERT INTO LagTest (Data) VALUES ('test3');

SELECT * 
FROM (
SELECT ID, Data, LAG(Data,1,0) OVER (ORDER BY ID) AS PreviousData
FROM LagTest 
) AS L
WHERE L.PreviousData <> L.Data;

The returns:

enter image description here

share|improve this answer
add comment

For sql 2005 and up, you can use row_number() function and CTE :

CREATE TABLE FieldTable
    ([ID] int, [Field1] int, [Field2] datetime, [Field3] varchar(12), [Field5] varchar(3), [Field7] varchar(11))
;

INSERT INTO FieldTable
    ([ID], [Field1], [Field2], [Field3], [Field5], [Field7])
VALUES
    (357, 357, '2013-03-07 00:00:00', '08:02:02:275', 't02', 'bBCD00103RG'),
    (365, 365, '2013-03-07 00:00:00', '08:02:05:307', 't02', 'bR U00103w'),
    (374, 374, '2013-03-07 00:00:00', '08:02:08:322', 't02', 'bR U00103w'),
    (474, 474, '2013-03-07 00:00:00', '08:02:41:307', 't02', 'bR U00103w'),
    (1378, 1378, '2013-03-07 00:00:00', '02:25:45:447', 't02', 'bR U00103w'),
    (1381, 1381, '2013-03-07 00:00:00', '02:25:46:416', 't02', 'bBFU3'),
    (1386, 1386, '2013-03-07 00:00:00', '02:25:49:057', 't02', 'bBFU02405LL'),
    (1394, 1394, '2013-03-07 00:00:00', '02:25:52:260', 't02', 'bBFU02405LL'),
    (1504, 1504, '2013-03-07 00:00:00', '02:26:42:307', 't02', 'bBFU02405LL'),
    (1510, 1510, '2013-03-07 00:00:00', '02:26:45:275', 't02', 'bBFU02405LL'),
    (1516, 1516, '2013-03-07 00:00:00', '02:26:48:307', 't02', 'bBFP02405LI'),
    (1523, 1523, '2013-03-07 00:00:00', '02:26:52:088', 't02', 'bBFP02405LI'),
    (1530, 1530, '2013-03-07 00:00:00', '02:26:54:885', 't02', 'bBFP02405LI'),
    (1556, 1556, '2013-03-07 00:00:00', '02:27:06:307', 't02', 'bBFP02405LI'),
    (1562, 1562, '2013-03-07 00:00:00', '02:27:09:307', 't02', 'bBFP02405LI'),
    (1568, 1568, '2013-03-07 00:00:00', '02:27:12:307', 't02', 'bR L02405o'),
    (1574, 1574, '2013-03-07 00:00:00', '02:27:15:338', 't02', 'bBCL/'),
    (1580, 1580, '2013-03-07 00:00:00', '02:27:18:635', 't02', 'bBCL00103RO'),
    (1587, 1587, '2013-03-07 00:00:00', '02:27:21:307', 't02', 'bBCL00103RO'),
    (1714, 1714, '2013-03-07 00:00:00', '02:28:21:291', 't02', 'bBCD00103RG'),
    (1721, 1721, '2013-03-07 00:00:00', '02:28:24:291', 't02', 'bBCD00103RG'),
    (1728, 1728, '2013-03-07 00:00:00', '02:28:27:338', 't02', 'bBCD00103RG'),
    (1734, 1734, '2013-03-07 00:00:00', '02:28:30:291', 't02', 'bBCD00103RG'),
    (1740, 1740, '2013-03-07 00:00:00', '02:28:33:447', 't02', 'bR U00103w'),
    (1996, 1996, '2013-03-07 00:00:00', '02:30:33:291', 't02', 'bR U00103w')
;

--- Solution :

;WITH cte
AS (
    SELECT *
        ,row_number() OVER (
            ORDER BY field3
            ) AS rownum
    FROM FieldTable
    )
SELECT C1.ID
    ,C1.Field1
    ,C1.Field2
    ,C1.Field3
    ,C1.Field5
    ,C1.Field7
FROM cte C1
INNER JOIN cte AS C2 
ON C1.rownum = C2.rownum + 1
WHERE C1.Field7 <> C2.Field7
ORDER BY C1.ID

SQLFIDDLE here.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.