CODEWITHSUNDEEP
mysqlsqlduplicatesgaps-and-islands
8128
8128

Reputation: 999

Filter out orphan table entries

Suppose there is a table with only two columns (an example is shown below). Every '1' entry should be followed (in the sorted order given below) by a '0'. However, as you can see, in the table, there are some 'orphans' where there are two consecutive '1's.

How can I create a query that returns all the rows, except for the first of any consecutive '1's? (This would reduce the example below from 16 rows to 14)

1 E
0 A
1 T
0 S
1 R
0 E
1 F
0 T
1 G
1 T
0 R
1 X
1 R
0 R
1 E
0 T

I'm going to try and clarify my problem, I think that above I simplified it too much. Imagine one table called logs, with four columns:

[The machine/time pair provides a unique key, as no machine can be logged in or out of twice at the same instant. Presumably an 'ID' column could be artificially created based on machine/time sort if needed.]

The idea is that every login event should be accompanied by a logout event. In an ideal word it would be fairly easy to match logins to logouts, and hence analyse the time spent logged in.

However, in the case of a power cut, the logout will not be recorded. Therefore (considering only one machine's data, sorted by time) if there are two login events in a row, we want to ignore the first login, because we don't have any reliable data from it. This is the problem I am trying to solve.

Upvotes: 1

Views: 200

Answers (4)

wildplasser
wildplasser

Reputation: 44250

USING a CTE to separate the lag-logic from the selection criteria.

DROP TABLE tmp.bits;
CREATE TABLE tmp.bits
    ( id SERIAL NOT NULL
    , bit INTEGER NOT NULL
    , code CHAR(1)
    );
INSERT INTO tmp.bits(bit, code) VALUES
(1, 'T' )
, (0, 'S' )
, (1, 'R' )
, (0, 'E' )
, (1, 'F' )
, (0, 'T' )
, (1, 'G' )
, (1, 'T' )
, (0, 'R' )
, (1, 'X' )
, (1, 'R' )
, (0, 'R' )
, (1, 'E' )
, (0, 'T' )
    ;

SET search_path='tmp';
SELECT * FROM bits;

-- EXPLAIN ANALYZE
WITH prevnext AS (
SELECT
    bt.id AS thisid
    , bt.bit  AS thisbit
    , bt.code AS thiscode
    , bp.bit AS prevbit
    , bp.code AS prevcode
    FROM bits bt
    LEFT JOIN bits bp ON (bt.id > bp.id)
    AND NOT EXISTS ( SELECT * FROM bits nx
        WHERE nx.id > bp.id
        AND nx.id < bt.id
        )   
    )
SELECT thisid, thisbit, thiscode
FROM prevnext
WHERE thisbit=0
OR prevbit IS NULL OR thisbit <> prevbit
    ;

EDIT:

for those poor soals that cannot use CTEs, it is easy to create a view instead:

CREATE VIEW prevnext AS (
SELECT
    bt.id AS thisid
    , bt.bit  AS thisbit
    ,bt.code AS thiscode
    , bp.bit AS prevbit
    , bp.code AS prevcode
    FROM bits bt
    LEFT JOIN bits bp ON (bt.id > bp.id)
    AND NOT EXISTS ( SELECT * FROM bits nx
        WHERE nx.id > bp.id
        AND nx.id < bt.id
        )
    )
    ;
SELECT thisid, thisbit, thiscode
FROM prevnext
WHERE thisbit=0
OR prevbit IS NULL OR thisbit <> prevbit
    ;

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657022

Provided, that

  • only 1's are dupes, never 0's
  • You want to get rid of all the first 1's if there are more.

Your text says "except for the first of any consecutive", but I think, this is what you want. Or there can only ever be 2, then it is the same.

SELECT x.*
FROM   x
LEFT   JOIN x y on y.id = (x.id + 1)
WHERE  (x.nr = y.nr) IS NOT TRUE -- OR x.nr = 0
ORDER  BY x.id

If you want to preserve double 0's, use the commented clause additionally, but probably not needed.

Edit after question edit:

You may want to add an auto-increment column to your data to make this simpler: Generate (i.e. write) a row number index column in MySQL

Other RDBMS (PostgreSQL, Oracle, SQL Server, ..) have window functions like row_number() or lag() and lead() that make such an operation much easier.

Upvotes: 1

user359040
user359040

Reputation:

Try:

select l.*
from logs l
where l.type = 0 or
      not (select type
           from (select * from logs order by `time` desc) n
           where n.machine = l.machine and
                 n.user = l.user and
                 n.time > l.time)
           group by () )

Upvotes: 0

Benoit
Benoit

Reputation: 79185

Assuming you get an id (add column, set column id = record number in database) use:

select a.*
  from the_table a
  left join the_table b on b.id = a.id + 1
                       and b.col1 = 0
 where a.col1 = 1
   and b.id is null

Upvotes: 0

Related Questions